1
2 -- ----------------------------------------------------
3 DECLARE @str varchar ( 500 )
4 SET @str = ' aaa,bbbx,eee,vv '
5 SELECT LEN ( REPLACE ( @str , ' , ' , ' ,, ' )) - LEN ( @str )
6
7
8 -- ----------------------------------------------------
9 DECLARE @b BIT
10 SET @b = 0
11 SELECT SUBSTRING (N ' 否是 ' , @b + 1 , 1 )
12
13 -- ----------------------------------------------------
14 DECLARE @ids varchar ( 50 )
15 SET @ids = ' 1,3 '
16 SELECT * FROM UserInfo WHERE CHARINDEX ( ' , ' + RTRIM (id) + ' , ' , ' , ' + @ids + ' , ' ) > 0 -- 考滤一下,这里为什么要在字串及ID前后加上逗号
17 -- ----------------------------------------------------
18 SELECT * INTO t1 FROM UserInfo WHERE 1 = 0
19
20
21 create function f_split( @str varchar ( 8000 ), @StrSeprate varchar ( 10 ))
22 returns @temp table (a varchar ( 100 ))
23 as
24 begin
25 declare @i int
26 set @str = rtrim ( ltrim ( @SourceSql ))
27 set @i = charindex ( @StrSeprate , @str )
28 while @i >= 1
29 begin
30 insert @temp values ( left ( @str , @i - 1 ))
31 set @str = substring ( @str , @i + 1 , len ( @str ) - @i ) -- 当然,这里您也可以改写为STUFF,可以自己试着改写一下
32 set @i = charindex ( @StrSeprate , @str )
33 end
34 if @SourceSql <> ' \ '
35 insert @temp values ( @str )
36 return
37 end
38
39 -- 利用创建临时表生成连续ID.
40 SELECT * , IDENTITY ( INT ) NID INTO #t FROM tb
41
42
43
44 select * from dbo.f_split( ' A:B:C:D:E ' , ' : ' )
45
2 -- ----------------------------------------------------
3 DECLARE @str varchar ( 500 )
4 SET @str = ' aaa,bbbx,eee,vv '
5 SELECT LEN ( REPLACE ( @str , ' , ' , ' ,, ' )) - LEN ( @str )
6
7
8 -- ----------------------------------------------------
9 DECLARE @b BIT
10 SET @b = 0
11 SELECT SUBSTRING (N ' 否是 ' , @b + 1 , 1 )
12
13 -- ----------------------------------------------------
14 DECLARE @ids varchar ( 50 )
15 SET @ids = ' 1,3 '
16 SELECT * FROM UserInfo WHERE CHARINDEX ( ' , ' + RTRIM (id) + ' , ' , ' , ' + @ids + ' , ' ) > 0 -- 考滤一下,这里为什么要在字串及ID前后加上逗号
17 -- ----------------------------------------------------
18 SELECT * INTO t1 FROM UserInfo WHERE 1 = 0
19
20
21 create function f_split( @str varchar ( 8000 ), @StrSeprate varchar ( 10 ))
22 returns @temp table (a varchar ( 100 ))
23 as
24 begin
25 declare @i int
26 set @str = rtrim ( ltrim ( @SourceSql ))
27 set @i = charindex ( @StrSeprate , @str )
28 while @i >= 1
29 begin
30 insert @temp values ( left ( @str , @i - 1 ))
31 set @str = substring ( @str , @i + 1 , len ( @str ) - @i ) -- 当然,这里您也可以改写为STUFF,可以自己试着改写一下
32 set @i = charindex ( @StrSeprate , @str )
33 end
34 if @SourceSql <> ' \ '
35 insert @temp values ( @str )
36 return
37 end
38
39 -- 利用创建临时表生成连续ID.
40 SELECT * , IDENTITY ( INT ) NID INTO #t FROM tb
41
42
43
44 select * from dbo.f_split( ' A:B:C:D:E ' , ' : ' )
45