下面的用户自定义函数(UDF)能把字符串解析为表(table).它也支持自定义分隔符.下面的脚本在SQL SERVER 2005下写的.该脚本也支持TEXT字段.如果你想在SQL SERVER 2000下是使用,请把VARCHAR(MAX) 替换为 VARCHAR(8000).
- CREATE FUNCTION dbo.udf_List2Table
- (
- @List VARCHAR(MAX),
- @Delim CHAR
- )
- RETURNS
- @ParsedList TABLE
- (
- item VARCHAR(MAX)
- )
- AS
- BEGIN
- DECLARE @item VARCHAR(MAX), @Pos INT
- SET @List = LTRIM(RTRIM(@List))+ @Delim
- SET @Pos = CHARINDEX(@Delim, @List, 1)
- WHILE @Pos > 0
- BEGIN
- SET @item = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
- IF @item <> ''
- BEGIN
- INSERT INTO @ParsedList (item)
- VALUES (CAST(@item AS VARCHAR(MAX)))
- END
- SET @List = RIGHT(@List, LEN(@List) - @Pos)
- SET @Pos = CHARINDEX(@Delim, @List, 1)
- END
- RETURN
- END
- GO
- SELECT item AS Example1
- FROM dbo.udf_List2Table('first||2nd||III||1+1+1+1','||')
- first
- 2nd
- III
- 1+1+1+1
- SELECT CAST(item AS INT) AS Example2
- FROM dbo.udf_List2Table('111,222,333,444,555',',')
- GO
- 111
- 222
- 333
- 444
- 555