How to create column by get everything after and before some characters-CodePudding

I have table in SQL Server like below:

select col from table

col1
------
02-567 City, ul. ABc 44, woj. Zak
56-123 City2, ul. Grt 78, woj. Maaap
44-153 Raw, Pl. 777, woj. Rapat

And I need to create query which will give me result like below:

col1                                  col2   col3
----------------------------------------------------
02-567 City, ul. ABc 44, woj. Zak     City   ul. ABc 44
56-123 City2, ul. Grt 78, woj. Maaap  City2  ul. Grt 78
44-153 Raw, Pl. 777, woj. Rapat       Raw    Pl. 777

So:

1. To create col2 I need to take everything after first space till first coma
1. To create col3 I need to take everything which start after space after first coma till second coma

How can I do than in SQL Server?

CodePudding user response：

select col1
, col2 = right(left(col1,charindex(',',col1)-1),charindex(',',col1)-1-charindex(' ',col1))
, col3 = ltrim(replace(reverse(parsename(replace(replace(reverse(col1),'.','•'),',','.'),2)),'•','.'))
from [table]

Ugly, I know.

CodePudding user response：

Perhaps a little less ugly :), but I suspect a nudge less performant than LukStorms' answer.

Example

Select  Col1
       ,Col2 =  stuff(Pos1,1,charindex(' ',Pos1 ' '),'')
       ,Col3 =  Pos2
 From  YourTable A
 Cross Apply ( Select  Pos1 =  trim(JSON_VALUE(S,'$[0]'))
                      ,Pos2 =  trim(JSON_VALUE(S,'$[1]'))
                From  (values ( '["' replace(string_escape([Col1],'json'),',','","') '"]' ) ) B1(S) 
             ) B

Results

Col1                                   Col2     Col3
02-567 City, ul. ABc 44, woj. Zak      City     ul. ABc 44
56-123 City2, ul. Grt 78, woj. Maaap   City2    ul. Grt 78
44-153 Raw, Pl. 777, woj. Rapat        Raw      Pl. 777