字符串拆分

USE tempdb

GO

---- 1. 测试表

IF OBJECT_ID('t') IS NOT NULL DROP TABLE t

GO

CREATE TABLE t(

    id INT IDENTITY(1,1) PRIMARY KEY,

    strings NVARCHAR(MAX)   

)

GO

INSERT INTO t (strings) VALUES('abc,def,ghijkl')

INSERT INTO t (strings) VALUES('1,2,3')

GO

---- 2. 增加一个字符串分割函数

IF OBJECT_ID('[dbo].[Fun_String2ToStringArray]') IS NOT NULL 

    DROP FUNCTION [dbo].[Fun_String2ToStringArray]

GO

CREATE FUNCTION [dbo].[Fun_String2ToStringArray](@str NVARCHAR(MAX), @split NVARCHAR(10))

RETURNS @table TABLE ([item] NVARCHAR(max))

AS

BEGIN

    IF LEN(@split) = 0

      BEGIN

        SET @split = N','

      END

 

    DECLARE @xml XML;

    SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')

 

    INSERT INTO @table

      SELECT item

      FROM   (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]

              FROM   @xml.nodes('/x') t(c)) t

      WHERE  item IS NOT NULL

 

    RETURN

END

GO

--3. 实际应用

----3.1 查询全部

SELECT t.*,f.item FROM t CROSS APPLY [dbo].[Fun_String2ToStringArray](strings, ',') AS f

/*

id    strings            item

1    abc,def,ghijkl    abc

1    abc,def,ghijkl    def

1    abc,def,ghijkl    ghijkl

2    1,2,3            1

2    1,2,3            2

2    1,2,3            3

*/

----3.2 查询是否包含某个值

SELECT t.*,f.item FROM t CROSS APPLY [dbo].[Fun_String2ToStringArray](strings, ',') AS f 

WHERE f.item =  'def' 

/*

id    strings            item

1    abc,def,ghijkl    def

*/