T-SQL 拆分使用指定分隔符的字符串(split string)

比如有一个表,我们需要些一个语句像SELECT OtherID, SplitData WHERE SomeID = 'abcdef-.......' , 然后就能返回分割成单独的行。

原表:

| SomeID         | OtherID     | Data

+----------------+-------------+-------------------

| abcdef-.....   | cdef123-... | 18,20,22

| abcdef-.....   | 4554a24-... | 17,19

| 987654-.....   | 12324a2-... | 13,19,20

 

预期结果:

| OtherID     | SplitData

+-------------+-------------------

| cdef123-... | 18

| cdef123-... | 20

| cdef123-... | 22

| 4554a24-... | 17

| 4554a24-... | 19

 

在 SQL Server 2016中引入了分割字符串函数STRING_SPLIT(详细参考MSDN),可以方便的实现。

select OtherID, SplitData
from yourtable
cross apply STRING_SPLIT (Data, ',') cs

 

在SQL Server 2016之前,必须添加一个自定义函数,具体有两种实现方式.

  1. XML解析法 -- 比较容易,适用于字符串能够转换为XML(不含有特殊字符也可以将特殊字符替换)

    CREATE FUNCTION [dbo].[SplitString] (

    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
    

    ) RETURNS TABLE WITH SCHEMABINDING AS

    RETURN
    ( 
       SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
       FROM
       (
         SELECT x = CONVERT(XML, '<i>'
           + REPLACE(@List, @Delimiter, '</i><i>')
           + '</i>').query('.')
       ) AS a CROSS APPLY x.nodes('i') AS y(i)
    );
    
  2. 递归法

    create function [dbo].splitString, @Splitter Varchar(99)) returns table as Return with tmp (DataItem, List , First) as

    (
              select @input  ,@input,   1  --first item ignored, set to get the type right
      union all
      select LEFT(List, CHARINDEX(@Splitter,List+@Splitter)-1),
                                     STUFF(List, 1, CHARINDEX(@Splitter,List+@Splitter), ''),
                                     0
              from tmp
              where List <> ''
    )
    
   select DataItem from tmp where first=0

使用方法:

select OtherID, SplitData
from yourtable
cross apply dbo.splitString (Data, ',') cs

本文由金沙官网线上发布于数据库,转载请注明出处:T-SQL 拆分使用指定分隔符的字符串(split string)

您可能还会对下面的文章感兴趣: