如何使用 T-SQL 使用分隔符字符拆分字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5096630/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to split string using delimiter char using T-SQL?
提问by User13839404
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'
My select statement is:-
我的选择语句是：-
Select col1, col2, col3 from Table01
But in Col3 I just need 'Client Name's value which is 'B B BOB'.
但是在 Col3 中，我只需要“客户端名称”的值，即“BB BOB”。
In Col3 -
在 Col3 -
Column delimiter is '|' pipe char (eg. 'Client ID = 4356hy')
Key Value delimiter is ' = ' equal to sign with one white space (leading and trailing).
列分隔符是“|” 管道字符（例如“客户端 ID = 4356hy”）
键值分隔符是 ' = ' 等于带一个空格的符号（前导和尾随）。
Please help.
请帮忙。
回答by RichardTheKiwi
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
EDIT - charindex vs patindex
编辑 - charindex 与 patindex
Test
测试
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
Timings
时间安排
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.
Conclusion
结论
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.
70 万次调用的 CharIndex 和 PatIndex 的时间相差在 3.5% 以内，所以我认为无论使用哪个都无关紧要。当两者都可以工作时，我可以互换使用它们。
回答by Thomas
You need a split function:
您需要一个拆分功能：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Function [dbo].[udf_Split]
(
@DelimitedList nvarchar(max)
, @Delimiter nvarchar(2) = ','
)
RETURNS TABLE
AS
RETURN
(
With CorrectedList As
(
Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
+ @DelimitedList
+ Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End
As List
, Len(@Delimiter) As DelimiterLen
)
, Numbers As
(
Select TOP( Coalesce(DataLength(@DelimitedList)/2,0) ) Row_Number() Over ( Order By c1.object_id ) As Value
From sys.columns As c1
Cross Join sys.columns As c2
)
Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Position
, Substring (
CL.List
, CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen
, CharIndex(@Delimiter, CL.list, N.Value + 1)
- ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen )
) As Value
From CorrectedList As CL
Cross Join Numbers As N
Where N.Value <= DataLength(CL.List) / 2
And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter
)
With your split function, you would then use Cross Apply to get the data:
使用 split 函数，您将使用 Cross Apply 来获取数据：
Select T.Col1, T.Col2
, Substring( Z.Value, 1, Charindex(' = ', Z.Value) - 1 ) As AttributeName
, Substring( Z.Value, Charindex(' = ', Z.Value) + 1, Len(Z.Value) ) As Value
From Table01 As T
Cross Apply dbo.udf_Split( T.Col3, '|' ) As Z
回答by MikeTWebb
You simply need to do a SUBSTR on the string in col3....
你只需要在 col3 中的字符串上做一个 SUBSTR ......
Select col1, col2, REPLACE(substr(col3, instr(col3, 'Client Name'),
(instr(col3, '|', instr(col3, 'Client Name') -
instr(col3, 'Client Name'))
),
'Client Name = ',
'')
from Table01
And yes, that is a bad DB design for the reasons stated in the original issue
是的，由于原始问题中所述的原因，这是一个糟糕的数据库设计
回答by user194076
It is terrible, but you can try to use
这很可怕，但你可以尝试使用
select
SUBSTRING(Table1.Col1,0,PATINDEX('%|%=',Table1.Col1)) as myString
from
Table1
This code is probably not 100% right though. need to be adjusted
不过，这段代码可能不是 100% 正确。需要调整
