mssql split

if
 
exists
 (
select
 
*
 
from
 dbo.sysobjects 
where
 id 
=
 
object_id
(N
'
[dbo].[f_splitSTR]
'
) 
and
 xtype 
in
 (N
'
FN
'
, N
'
IF
'
, N
'
TF
'
))


drop
 
function
 
[
dbo
]
.
[
f_splitSTR
]



GO





--
3.2.1 循环截取法



CREATE
 
FUNCTION
 f_splitSTR(


@s
   
varchar
(
8000
),   
--
待分拆的字符串



@split
 
varchar
(
10
)     
--
数据分隔符



)
RETURNS
 
@re
 
TABLE
(col 
varchar
(
100
))


AS



BEGIN


    
DECLARE
 
@splitlen
 
int


    
SET
 
@splitlen
=
LEN
(
@split
+
'
a
'
)
-
2


    
WHILE
 
CHARINDEX
(
@split
,
@s
)
>
0


    
BEGIN


        
INSERT
 
@re
 
VALUES
(
LEFT
(
@s
,
CHARINDEX
(
@split
,
@s
)
-
1
))

        
SET
 
@s
=
STUFF
(
@s
,
1
,
CHARINDEX
(
@split
,
@s
)
+
@splitlen
,
''
)

    
END


    
INSERT
 
@re
 
VALUES
(
@s
)

    
RETURN



END



declare
 
@str
 
as
 
varchar
(
8000
) 


set
 
@str
=
'
1-101,2-201,3-306,4-405,5-501,6-602
'
 


declare
 
@key
 
as
 
datetime
 


set
 
@key
 
=
'
2009/08/12
'
 


declare
 
@t
 
table
 

( 


Day
 
varchar
(
10
) 

,A 
varchar
(
10
) 

,B 
int
 

)


insert
 
into
 
@t
(A) 


select
 
*
 
from
  dbo.f_splitSTR(
@str
,
'
,
'
)


update
 
@t



set
 
Day
=
convert
(
varchar
(
10
),
@key
,
111
),

   A
=LEFT
(a,
1
)

   ,B
=RIGHT
(a,
3
)

 
select
 
*
 
from
 
@t
 

 
/*


 

Day        A          B

---------- ---------- -----------

2009/08/12 1          101

2009/08/12 2          201

2009/08/12 3          306

2009/08/12 4          405

2009/08/12 5          501

2009/08/12 6          602





(6 行受影响)





 
*/