偶然在电脑里看到以前保存的这个函数，是将一个单独字符串切分成一组字符串，这里分隔符是英文逗号“，” 遇到其他情况只要稍加修改就好了
CREATE FUNCTION dbo.f_splitstr(
@str varchar(8000)
)RETURNS @r TABLE(id int IDENTITY(1, 1), value varchar(5000))
AS
BEGIN
/* Function body */
DECLARE @pos int
SET @pos = CHARINDEX(',', @str)
WHILE @pos > 0
BEGIN
INSERT @r(value) VALUES(LEFT(@str, @pos - 1))
SELECT
@str = STUFF(@str, 1, @pos, ''),
@pos = CHARINDEX(',', @str)
END
IF @str > ''
INSERT @r(value) VALUES(@str)
RETURN
END
截图如下
2011-11-15 10:15:28
今天在园子里看到另外一个实现方法，不妨借鉴一下：
SET
ANSI_NULLS
ON
GO
SET
QUOTED_IDENTIFIER
ON
GO
/*
by kudychen 2011-9-28
*/
CREATE
function
[
dbo
]
.
[
SplitString
]
(
@Input
nvarchar
(
max
),
--
input string to be separated
@Separator
nvarchar
(
max
)
=
'
,
'
,
--
a string that delimit the substrings in the input string
@RemoveEmptyEntries
bit
=
1
--
the return value does not include array elements that contain an empty string
)
returns
@TABLE
table
(
[
Id
]
int
identity
(
1
,
1
),
[
Value
]
nvarchar
(
max
))
as
begin
declare
@Index
int
,
@Entry
nvarchar
(
max
)
set
@Index
=
charindex
(
@Separator
,
@Input
)
while
(
@Index
>
0
)
begin
set
@Entry
=
ltrim
(
rtrim
(
substring
(
@Input
,
1
,
@Index
-
1
)))
if
(
@RemoveEmptyEntries
=
0
)
or
(
@RemoveEmptyEntries
=
1
and
@Entry
<>
''
)
begin
insert
into
@TABLE
(
[
Value
]
)
Values
(
@Entry
)
end
set
@Input
=
substring
(
@Input
,
@Index
+
datalength
(
@Separator
)
/
2
,
len
(
@Input
))
set
@Index
=
charindex
(
@Separator
,
@Input
)
end
set
@Entry
=
ltrim
(
rtrim
(
@Input
))
if
(
@RemoveEmptyEntries
=
0
)
or
(
@RemoveEmptyEntries
=
1
and
@Entry
<>
''
)
begin
insert
into
@TABLE
(
[
Value
]
)
Values
(
@Entry
)
end
return
end
使用方法
declare
@str1
varchar
(
max
),
@str2
varchar
(
max
),
@str3
varchar
(
max
)
set
@str1
=
'
1,2,3
'
set
@str2
=
'
1###2###3
'
set
@str3
=
'
1###2###3###
'
select
[
Value
]
from
[
dbo
]
.
[
SplitString
]
(
@str1
,
'
,
'
,
1
)
select
[
Value
]
from
[
dbo
]
.
[
SplitString
]
(
@str2
,
'
###
'
,
1
)
select
[
Value
]
from
[
dbo
]
.
[
SplitString
]
(
@str3
,
'
###
'
,
0
)
里面还有个自增的[Id]字段哦，在某些情况下有可能会用上的，例如根据Id来保存排序等等。
例如根据某表的ID保存排序：
update a set a.[Order]=t.[Id]
from [dbo].[表] as a join [dbo].SplitString('1,2,3', ',', 1) as t on a.[Id]=t.[Value]
具体的应用请根据自己的情况来吧
