IF exists (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [f_split]') and xtype in (n'fn ', n'if ", n')) DROP FUNCTION [DBO] [f_split] Go
/ * - Get the character string list specified location You can customize the separator of the string list If you take the range, return empty string
- Zou Jian 2004.07 (Please keep this information) - * /
/ * - Call example
- Test data declare @t table (fitem varchar (100)) Insert @t select '100.120.10' union all select '20 .140.10 'union all select' 150.124.150.10 '- Query Select FiteM1 = dbo.f_split ( Fitem, 1, '.'), fitem2 = dbo.f_split (fitem, 2, '), fitem3 = dbo.f_split (fitem, 3,'. '), fitem4 = dbo.f_split (Fitem, 4,' . ') From @T - * / create function f_split (@s varchar (8000), - string list @POS int, - Number location @Splitchar varchar (10) - Delivery) Returns varchar (8000 ) asbegin declare @i int, @ i 网
Select @ i = charindex (@ splitchar, @ s), @ i = len (@splitchar) While @i> 0 and @POS> 1 select @ s = substring (@ s, @ i @ l, 8000), @ i = Charindex (@ splitchar, @ s), @ POS = @ POS-1 Return (Case @Pos When 1 Ten Case When @i> 0 Then LEFT (@ s, @ i-1) else @s END ELSE 'END ) ENDGO
