如何从SQL消息中查找子字符串？

I have data like:-

我有以下数据： -

1)Your assigned carrier is {carrier_name}. They will be reaching out to you soon to schedule pick-up, but feel free to call them at {carrier_phone} and reference {reference_id}. They are open now and will be until {close_time}.

1）您指定的运营商是{carrier_name}。他们会尽快与您联系以安排接机，但可以随时致电{carrier_phone}并参考{reference_id}。它们现在是开放的，直到{close_time}。

2)The {vehicle_owner} indicated that their {body_style} was picked up. Would you provide an update on the assignment or mark it complete?

2）{vehicle_owner}表示他们的{body_style}已被选中。您是否会提供有关作业的更新或标记完成？

I want to find all the values between brackets -> {___}.

我想找到括号之间的所有值 - > {___}。

Only particular message should be found between brackets. There could be any value between them.

在括号中只能找到特定的消息。他们之间可能有任何价值。

How can I find it out using query?

如何使用查询找到它？

2 个解决方案

#1

If the string always follows the repeating pattern of '..{..})' one method to solve this uses a CSV Splitter function by Jeff Moden, replacing the second delimiter with the first delimiter, and getting only the second sets using modulo (%):

如果字符串始终遵循'.. {..}）的重复模式，则解决此问题的一种方法使用Jeff Moden的CSV Splitter函数，用第一个分隔符替换第二个分隔符，并使用modulo仅获取第二个分隔符（ ％）：

select 
    Id
 , col = x.item
from t
  cross apply (
    select Item = ltrim(rtrim(i.Item))
      from [dbo].[delimitedsplit8K](replace(t.col,'}','{'),'{') as i
      where ItemNumber%2=0
      ) x

test setup: http://rextester.com/VDBK82975

测试设置：http：//rextester.com/VDBK82975

returns:

收益：

+----+---------------+
| Id |      col      |
+----+---------------+
|  1 | carrier_name  |
|  1 | carrier_phone |
|  1 | reference_id  |
|  1 | close_time    |
|  2 | vehicle_owner |
|  2 | body_style    |
+----+---------------+

Splitting strings reference:

拆分字符串参考：

• Tally OH! An Improved SQL 8K “CSV Splitter” Function - Jeff Moden
• Tally OH！改进的SQL 8K“CSV分离器”功能 - Jeff Moden
• Splitting Strings : A Follow-Up - Aaron Bertrand
• 分裂字符串：后续行动 - Aaron Bertrand
• Split strings the right way – or the next best way - Aaron Bertrand
• 以正确的方式分割琴弦 - 或者是下一个最好的方式 - Aaron Bertrand
• string_split() in SQL Server 2016 : Follow-Up #1 - Aaron Bertrand
• SQL Server 2016中的string_split（）：后续＃1 - Aaron Bertrand

---

function used in the test:

测试中使用的函数：

create function [dbo].[delimitedsplit8K] (
      @pstring varchar(8000)
    , @pdelimiter char(1)
  )
returns table with schemabinding as
 return
  with e1(N) as (
    select 1 union all select 1 union all select 1 union all 
    select 1 union all select 1 union all select 1 union all 
    select 1 union all select 1 union all select 1 union all select 1
  )
  , e2(N) as (select 1 from e1 a, e1 b)
  , e4(N) as (select 1 from e2 a, e2 b)
  , ctetally(N) as (
    select top (isnull(datalength(@pstring),0)) 
      row_number() over (order by (select null)) from e4
  )
  , ctestart(N1) as (
    select 1 union all
    select t.N+1 from ctetally t where substring(@pstring,t.N,1) = @pdelimiter
  )
  , ctelen(N1,L1) as (
    select s.N1,
      isnull(nullif(charindex(@pdelimiter,@pstring,s.N1),0)-s.N1,8000)
    from ctestart s
  )
 select itemnumber = row_number() over(order by l.N1)
      , item       = substring(@pstring, l.N1, l.L1)
   from ctelen l
;

---

Recursive cte version (no additional function required)

递归cte版本（无需额外功能）

;with cte as (
  select 
      id
    , val = left(stuff(col, 1, charindex('{', col),'')
              , charindex('}', col) - charindex('{', col) - 1
            ) 
    , rest = stuff(col, 1, charindex('}', col) + 1,'') 
  from t
  where col like '%{%}%'
  union all
  select 
      id
    , val = left(stuff(rest, 1, charindex('{', rest),'')
              , charindex('}', rest) - charindex('{', rest) - 1
            ) 
    , rest = stuff(rest, 1, charindex('}', rest) + 1,'') 
  from cte
  where rest like '%{%}%'
)
select id, val
from cte
order by id, val;

returns:

收益：

+----+---------------+
| Id |      col      |
+----+---------------+
|  1 | carrier_name  |
|  1 | carrier_phone |
|  1 | reference_id  |
|  1 | close_time    |
|  2 | vehicle_owner |
|  2 | body_style    |
+----+---------------+

#2

This can be achieved through SUBSTRING and CHARINDEX

这可以通过SUBSTRING和CHARINDEX实现

SUBSTRING(@Text, CHARINDEX('%{%',@Text), CHARINDEX('%}%',@Text))

CHARINDEX gives the value of the first occurence of the search string. Providing those value in SUBSTRING, you can get the desired output.

CHARINDEX给出搜索字符串第一次出现的值。在SUBSTRING中提供这些值，您可以获得所需的输出。