我们常常碰到需要将用户输入之字符串依特定的分隔符转换为表的一个栏位,下面是我写的一个sql函数.如能灵活运用则用途广泛.
-- =============================================
-- Author: honhwa
-- Create date: 2014/09/02
-- Description: Convert String to Table
-- =============================================
CREATE FUNCTION [dbo].[fn_StringToTable]
(
@str NVARCHAR(MAX) = '', @split NVARCHAR(30) = ','
)
RETURNS
@TempView TABLE
(
Cond NVARCHAR(MAX), Seq INT
)
AS
BEGIN
DECLARE @i INT = 1, @seq INT = 1
DECLARE @cond NVARCHAR(MAX) = '' SELECT @str = @str + @split WHILE CHARINDEX(@split, @str , @i) > 0 Begin
SELECT @cond = SUBSTRING(@str, @i, CHARINDEX(@split, @str, @i) - @i)
IF @cond <> ''
INSERT @TempView(Cond, Seq) VALUES(@cond, @seq)
SELECT @i = CHARINDEX(@split, @str, @i) + 1, @seq = @seq + 1
END
RETURN
END
示例 : SELECT * FROM fn_StringToTable('aaa,bbb,ccc,ddd,eee,fffff,sssss', ',') fstt
Cond Seq
aaa 1
bbb 2
ccc 3
ddd 4
eee 5
fffff 6
sssss 7