--将传入的一个参数按指定分隔符切分到一个表中

DECLARE @SourceIDs varchar(max)
SET @SourceIDs = 'a,bcd,123,+-*/=,x&y,<key>'
SELECT v = x.n.value('.','varchar(10)')
FROM (
    SELECT ValuesXML = CAST('<root>' +
        REPLACE((SELECT v = @SourceIDs FOR XML PATH('')),',','</v><v>') +
        '</root>' AS XML)
) t
CROSS APPLY t.ValuesXML.nodes('/root/v') x(n)

 

------------

自己验证如下

 

写成一个函数

 

CREATE FUNCTION dbo.Fun_SplitWord(@FieldName VARCHAR(4000))
RETURNS  @Table TABLE (SplitFiled VARCHAR(6)
         )
AS
BEGIN
  INSERT INTO @Table
  SELECT v = x.n.value('.','varchar(10)')
  FROM (
   SELECT ValuesXML = CAST('<root>' +
    REPLACE((SELECT v = @FieldName FOR XML PATH('')),',','</v><v>') +
    '</root>' AS XML)
  ) t
  CROSS APPLY t.ValuesXML.nodes('/root/v') x(n)

  RETURN
END

 

-------------------------

 

 调用

SELECT * FROM dbo.Fun_SplitWord('aaaa,bb,cc')

 

 

 

下一节讲到的方法就能够解决上述问题；