如何使用 T-SQL 使用分隔符字符拆分字符串? [英] How to split string using delimiter char using T-SQL?

问题描述

我在表的一列中有这个长字符串.我只想获得特定信息:-我的表结构:-

I have this long string in one of the columns of the table. I want to get only specific information:- My Table structure:-

Col1 = '123'
Col2 = 'AAAAA'
Col3 = 'Clent ID = 4356hy|Client Name = B B BOB|Client Phone = 667-444-2626|Client Fax = 666-666-0151|Info = INF8888877 -MAC333330554/444400800'

我的选择语句是:-

Select col1, col2, col3 from Table01

但在 Col3 中，我只需要客户端名称"的值，即B B BOB".

But in Col3 I just need 'Client Name's value which is 'B B BOB'.

在 Col3 中 -

• 列分隔符是|"管道字符(例如客户端 ID = 4356hy")

• Column delimiter is '|' pipe char (eg. 'Client ID = 4356hy')

键值分隔符是 ' = ' 等于带一个空格的符号(前导和尾随).

Key Value delimiter is ' = ' equal to sign with one white space (leading and trailing).

请帮忙.

推荐答案

对于你的具体数据，你可以使用

For your specific data, you can use

Select col1, col2, LTRIM(RTRIM(SUBSTRING(
    STUFF(col3, CHARINDEX('|', col3,
    PATINDEX('%|Client Name =%', col3) + 14), 1000, ''),
    PATINDEX('%|Client Name =%', col3) + 14, 1000))) col3
from Table01

编辑 - charindex 与 patindex

测试

select col3='Clent ID = 4356hy|Client Name = B B BOB|Client Phone = 667-444-2626|Client Fax = 666-666-0151|Info = INF8888877 -MAC333330554/444400800'
into t1m
from master..spt_values a
cross join master..spt_values b
where a.number < 100
-- (711704 row(s) affected)

set statistics time on

dbcc dropcleanbuffers
dbcc freeproccache
select a=CHARINDEX('|Client Name =', col3) into #tmp1 from t1m
drop table #tmp1

dbcc dropcleanbuffers
dbcc freeproccache
select a=PATINDEX('%|Client Name =%', col3) into #tmp2 from t1m
drop table #tmp2

set statistics time off

时间

CHARINDEX:

 SQL Server Execution Times (1):
   CPU time = 5656 ms,  elapsed time = 6418 ms.
 SQL Server Execution Times (2):
   CPU time = 5813 ms,  elapsed time = 6114 ms.
 SQL Server Execution Times (3):
   CPU time = 5672 ms,  elapsed time = 6108 ms.

PATINDEX:

 SQL Server Execution Times (1):
   CPU time = 5906 ms,  elapsed time = 6296 ms.
 SQL Server Execution Times (2):
   CPU time = 5860 ms,  elapsed time = 6404 ms.
 SQL Server Execution Times (3):
   CPU time = 6109 ms,  elapsed time = 6301 ms.

结论

CharIndex 和 PatIndex 用于 700k 调用的时间彼此相差在 3.5% 以内，所以我认为无论使用哪个都无关紧要.当两者都可以工作时，我会交替使用它们.

The timings for CharIndex and PatIndex for 700k calls are within 3.5% of each other, so I don't think it would matter whichever is used. I use them interchangeably when both can work.

这篇关于如何使用 T-SQL 使用分隔符字符拆分字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！