通过正则表达式匹配将 SQL 列拆分为多行 [英] Split SQL Column into Multiple Rows by Regex Match

问题描述

我正在将一个 NTEXT 列转换为多个记录.我希望通过新行或 json 对象拆分原始列.可以肯定的是，这是一个独特的场景，但在 sql 环境之外，此正则表达式正确匹配了我从原始列中需要的所有内容:

I'm in the middle of converting an NTEXT column into multiple records. I'm looking to split the original column by new line or json object. It's a unique scenario, to be sure, but outside of a sql environment this regex correctly matches everything I need from the original column:

({(.*)(.*\r\n)*?})|(.+\r\n).

如果我有一个包含值的列的记录:

If I have a record with a column that has the value:

Foo bar baz
hello world
{
  foo: 'bar',
  bar: 'foo'
}
{
  foo: 'foo',
  bar: 'bar'
}

我想把它分成多条记录:

I want to break it into multiple records:

| ID | Text         |
---------------------
|  1 | Foo bar baz  |

|  2 | hello world  |

|  3 | {            |
|    |   foo: 'bar' |
|    |   bar: 'foo' |
|    | }            |

|  4 | {            |
|    |   foo: 'foo' |
|    |   bar: 'bar' |
|    | }            |

有什么简单的方法可以做到这一点?这是一个 SQL Express 服务器.

Any easy way to accomplish this? It's a SQL Express server.

推荐答案

借助拆分/解析功能

Declare @String varchar(max)='Foo bar baz
hello world
{
  foo: ''bar'',
  bar: ''foo''
}
{
  foo: ''foo'',
  bar: ''bar''
}'

Select ID=Row_Number() over (Order By (Select NULL))
      ,Text = B.RetVal
 From (Select RetSeq,RetVal = IIF(CharIndex('}',RetVal)>0,'{'+RetVal,RetVal) from [dbo].[udf-Str-Parse](@String,'{')) A
 Cross Apply (
               Select * from [dbo].[udf-Str-Parse](A.RetVal,IIF(CharIndex('{',A.RetVal)>0,char(1),char(10)))
             ) B
 Where B.RetVal is Not Null

退货

ID  Text
1   Foo bar baz
2   hello world
3   {
     foo: 'bar',
     bar: 'foo'
    }

4   {
     foo: 'foo',
     bar: 'bar'
    }

UDF(如果需要)

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From (Select x = Cast('<x>'+ Replace(@String,@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Performance On a 5,000 random sample -8K 77.8ms, -1M 79ms (+1.16), -- 91.66ms (+13.8)

这篇关于通过正则表达式匹配将 SQL 列拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！