我有类似的数据: -
1)您分配的载体是{carrier_name}。他们将尽快与您联系以安排接送服务,但请随时致电{Carrier_phone}并参考{reference_id}。他们现在开放,直到{close_time}。
2){ware_owner}表示他们的{body_style}被拾取了。您会提供有关作业的更新还是将其标记为完成?
我想在括号 - &gt之间找到所有值{___}
。
在括号之间只能找到特定的消息。它们之间可能有任何价值。
如何使用查询找到它?
如果字符串始终遵循 '..{..})'
的重复模式,一种方法来解决此方法,使用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
测试设置:http://rextester.com/vdbk82975
返回:
+----+---------------+
| Id | col |
+----+---------------+
| 1 | carrier_name |
| 1 | carrier_phone |
| 1 | reference_id |
| 1 | close_time |
| 2 | vehicle_owner |
| 2 | body_style |
+----+---------------+
拆分字符串参考:
- Tally哦!改进的SQL 8K" CSV分离器"功能-Jeff Moden
- 分裂字符串:后续 - 亚伦·伯特兰(Aaron Bertrand)
- 以正确的方式分开字符串 - 或下一个最好的方法 - 亚伦·伯特兰(Aaron Bertrand)
-
string_split()
在SQL Server 2016:后续#1 -Aaron Bertrand
测试中使用的功能:
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
;
递归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;
返回:
+----+---------------+
| Id | col |
+----+---------------+
| 1 | carrier_name |
| 1 | carrier_phone |
| 1 | reference_id |
| 1 | close_time |
| 2 | vehicle_owner |
| 2 | body_style |
+----+---------------+
这可以通过 substring 和 Charindex
来实现
SUBSTRING(@Text, CHARINDEX('%{%',@Text), CHARINDEX('%}%',@Text))
Charindex给出了搜索字符串的第一次出现的值。在子字符串中提供这些值,您可以获取所需的输出。