SQL Server : extract strings from delimiter-CodePudding

I'm query a column called Description and I need to extract strings from each of the "-" delimiters;

e.g.

Description            
---------------------------------
abc@abc.com - Invoice - A12222203
FGH@fgh.com - Credit -  C12222333

So ideally need to extract each segment into three separate columns;

e.g.

Email       | Doc Type | Ref       
------------ ---------- ----------
abc@abc.com | Invoice  | A12222203 
FGH@fgh.com | Credit   | C12222333

I have managed to extract the email address using

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

Any ideas how I can split the two remaining sections into individual columns (i.e. Doc type and ref)?

Many thanks

CodePudding user response：

There must be hundreds of ways to do this string manipulation, here are a couple.

This uses apply to get the positions of each delimeter then simple string manipulation to get each part.

with myTable as (
    select * from (values('[email protected] - Invoice - A12222203'),('[email protected] - 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)

This splits the string into rows then conditionally aggregates back into one row.

with myTable as (
    select * from (values('[email protected] - Invoice - A12222203'),('[email protected] - 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