SQL Server:从分隔符中提取字符串

Question

我正在查询一个名为Description的列，我需要从每个"-">

分隔符;。

Description            
---------------------------------
[e-mail] - Invoice - A12222203
[e-mail] - Credit -  C12222333

所以理想情况下需要将每个片段提取为三个单独的列;

。

Email       | Doc Type | Ref       
------------+----------+----------
[e-mail] | Invoice  | A12222203 
[e-mail] | Credit   | C12222333

我已经设法使用

提取电子邮件地址

Substring(SL_Reference,0,charindex('-',SL_Reference))Email

任何想法如何我可以分成单独的列(即Doc类型和ref)剩下的两个部分?

多谢

Answer 1

必须有数百种方法来做这个字符串操作，这里有几个。

这使用apply来获得每个分隔符的位置，然后使用简单的字符串操作来获得每个部分。

with myTable as (
select * from (values('[e-mail] - Invoice - A12222203'),('[e-mail] - Credit - C12222333'))v(Description)
)
select
Trim(Left(description,h1-1)) Email, 
Trim(Substring(description,h1+1,Len(description)-h2-h1-1)) DocType,
Trim(Right(description,h2-1)) Ref
from mytable
cross apply(values(CharIndex('-',description)))v1(h1)
cross apply(values(CharIndex('-',Reverse(description))))v2(h2)

这将字符串分为行那么有条件地聚集回一行。

with myTable as (
select * from (values('[e-mail] - Invoice - A12222203'),('[e-mail] - Credit -  C12222333'))v(Description)
)
select 
max(Iif(rn=1,v,null)) Email,
max(Iif(rn=2,v,null)) Doctype,
max(Iif(rn=3,v,null)) Ref
from mytable
cross apply (
select Trim(value)v,row_number() over(order by (select null)) rn
from String_Split(Description,'-') 
)s
group by Description