CODEWITHSUNDEEP
sql-server-2008
Aparanjit
Aparanjit

Reputation: 41

I need to split a column into 3 different columns

I have a column which has the values as '150/250/555','114/1254/5241','45127/521/102'. So in this way I have a column. Now I need to split this column into three different columns. Say in example '150/250/555', I need to split 150 value into one column, 250 into another column and similarly 555 into 3rd column.

I got for the first column by using this query:

select substring(elt.EMPLR_LIAB_TYP_NM,1,CHARINDEX('/',elt.EMPLR_LIAB_TYP_NM)-1)
From EMPLOYER_LIABILITY_TYPE elt

This query is successfully giving me the value of 1 first column which is 150 in example '150/250/555'.

But I am little confused how to do it for 2nd and 3rd columns which should get the values 250 in 2nd column and 555 in 3rd column after '/'. Can anyone help me in this?

Upvotes: 0

Views: 161

Answers (1)

Bohemian
Bohemian

Reputation: 425033

Sqlserver's charindex() function has an optional 3rd parameter that specifies what character the search is to start at. You can use this to start your search one character after where you found the previous delimiter.

select
    substring(EMPLR_LIAB_TYP_NM, 1, CHARINDEX('/',EMPLR_LIAB_TYP_NM)-1) col1,
    substring(EMPLR_LIAB_TYP_NM, CHARINDEX('/',EMPLR_LIAB_TYP_NM)+1,
        CHARINDEX('/', EMPLR_LIAB_TYP_NM, CHARINDEX('/', EMPLR_LIAB_TYP_NM) + 1) - CHARINDEX('/',EMPLR_LIAB_TYP_NM) - 1) col2,
    reverse(substring(reverse(EMPLR_LIAB_TYP_NM),1,CHARINDEX('/',reverse(EMPLR_LIAB_TYP_NM))-1)) col3
from EMPLOYER_LIABILITY_TYPE

See SQLFiddle

It's a bit of a monster, due to SQLServer's lack of a split() function. I used reverse() to allow a version of the first column's expression to work for the end column (instead of yet more nested calls to charindex()).

Upvotes: 2

Related Questions