sqlsql-servert-sqlsubstringcharindex

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


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:

How can I do than in SQL Server?


Solution

  • 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.