--
SQL Server Split函数
--
Author: sq
--
说明：被分割后的字段为:short_str
--
支持分割符多字节
--
使用方法
--Select * FROM splits(
'
1203401230105045
'
,
'
0
'
)
--Select * FROM splits('1203401230105045','123'
)
--
select t1.id,t2.short_str from Table1 as t1
cross apply Splits(t1.name,',') as t2
--
cross apply 为2005以上版本才有的函数
CREATE FUNCTION splits(@Long_str NVARCHAR(MAX),@split_str NVARCHAR(
100
))
RETURNS @tmp TABLE(
ID inT IDENTITY PRIMARY KEY,
short_str NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @short_str NVARCHAR(MAX),@split_str_length
int
,@split_str_Position_Begin
int
SET @split_str_length
=
LEN(@split_str)
SET @Long_str
=REPLACE(REPLACE(@Long_str,CHAR(
10
),
''
),CHAR(
13
),
''
)
IF CHARINDEX(@split_str,@Long_str)
=
1
SET @Long_str
=STUFF(@Long_str,
1
,@split_str_length,
''
)
IF CHARINDEX(@split_str,@Long_str)
=
0
INSERT INTO @tmp SELECT @Long_str
ELSE
BEGIN
WHILE
1
>
0
BEGIN
SET @split_str_Position_Begin
=
CHARINDEX(@split_str,@Long_str)
SET @short_str
=LEFT(@Long_str,@split_str_Position_Begin-
1
)
IF @short_str
<>
''
INSERT INTO @tmp SELECT @short_str
SET @Long_str
=STUFF(@Long_str,
1
,@split_str_Position_Begin+@split_str_length-
1
,
''
)
SET @split_str_Position_Begin
=
CHARINDEX(@split_str,@Long_str)
IF @split_str_Position_Begin
=
0
BEGIN
IF LTRIM(@Long_str)
<>
''
INSERT INTO @tmp SELECT @Long_str
BREAK
END
END
END
RETURN
END
