SQL字符串分割解析

SQL字符串分割解析
常⽤以下三种：
【1】substring( expression ,start , length )；
【2】CHARINDEX ( expression1 , expression2 [ , start_location ] ) ；
【3】LEFT(expression,start)；
注意：
(1)substring中：对于start，负数和0都是空，真正有意义的位置是从" 1 "开始。

(2)CHARINDEX中：expression1是要到expression2中寻找的字符，start_location是CHARINDEX函数开始在expression2中找expression1的位置。

(3)LEFT中：表⽰截取expression的start索引位置左侧的字符串（包括索引位置在内）。

⽅法⼀：动态SQL法
DECLARE@s VARCHAR(50),@sql VARCHAR(300)
SET@s='1,2,3,4,5,6,7,8,9,10'
SET@sql='SELECT col='''+REPLACE(@s,',',''' UNION ALL SELECT ''')+''''
PRINT@sql
EXEC (@sql)
⽅法⼆：循环截取法
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
CREATE FUNCTION f_splitSTR(
@s VARCHAR(8000), --待分拆的字符串
@split VARCHAR(10) --数据分隔符
)RETURNS@re TABLE(col VARCHAR(100))
AS
BEGIN
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT@re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
--删除⽬标字符串从start开始length并插⼊指定字符串
SET@s=STUFF(@s,1,CHARINDEX(@split,@s),'')
--Set @s = Substring(@s, CharIndex(@split,@s)+@splitle, 100)
END
INSERT@re VALUES(@s)
RETURN
END
GO
⽅法三：使⽤临时性分拆辅助表法
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
CREATE FUNCTION f_splitSTR(
@s VARCHAR(8000), --待分拆的字符串
@split VARCHAR(10) --数据分隔符
)RETURNS@re TABLE(col VARCHAR(100))
AS
BEGIN
--创建分拆处理的辅助表(⽤户定义函数中只能操作表变量)
DECLARE@t TABLE(ID INT IDENTITY,b BIT)
INSERT@t(b) SELECT TOP80000FROM syscolumns va
INSERT@re SELECT SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID)
FROM@t WHERE ID<=LEN(@s) AND CHARINDEX(@split,@s+@split,ID)=ID
RETURN
END
GO
⽅法四：使⽤永久性分拆辅助表法
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
IF EXISTS (
SELECT*FROM dbo.sysobjects
WHERE id =OBJECT_ID(N'[dbo].[tb_splitSTR]') and objectproperty(id,N'IsUserTable')=1
)
DROP TABLE[dbo].[tb_splitSTR]
GO
--字符串分拆辅助表
SELECT TOP8000 ID=IDENTITY(int,1,1) INTO dbo.tb_splitSTR FROM syscolumns a
GO
--字符串分拆处理函数
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS TABLE
AS
RETURN(
SELECT col=CAST(SUBSTRING(@s,ID,CHARINDEX(@split,@s+@split,ID)-ID) as varchar(100)) FROM tb_splitSTR
WHERE ID<=LEN(@s) AND CHARINDEX(@split,@s+@split,ID)=ID)
GO
⽅法五：利⽤sql server2005的OUTER APPLY
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
CREATE FUNCTION[dbo].[f_splitSTR]
(
@str VARCHAR(MAX) ,
@split VARCHAR(10)
)
RETURNS TABLE
AS
RETURN
( SELECT B.id
FROM ( SELECT[value]=CONVERT(XML , '<v>'+REPLACE(@str , @split , '</v><v>') +'</v>')
) A
OUTER APPLY ( SELECT id = N.v.value('.' , 'varchar(100)')
FROM A.[value].nodes('/v') N ( v )
) B
)。