How to split string in multiple column in sql server

Question

I have string "1:182:1,1:195:2,1:213:1". I spllited the string with ',' in different rows. Now I want each rows single column to be splitted in 3 different columns in same row.

I tried using

     SELECT   LEFT(ThemeProperty, CHARINDEX(':', ThemeProperty) - 1) ,
            RIGHT(ThemeProperty,
                  LEN(ThemeProperty) - CHARINDEX(':', ThemeProperty))
   FROM     #tempThemeProperty

But its output is (No column name) (No column name) 1 182:1 1 195:2 1 213:1

But I want it to be

Column1 Column2 Column3
1       182     1

So any help would be appreciated.

Just use second charindex to find the second : – James Z Aug 21 '15 at 08:09 — James Z, Aug 21 '15 at 08:09
is your problem solved from above answer – wiretext Aug 21 '15 at 08:35 — wiretext, Aug 21 '15 at 08:35

score 2 · Answer 1 · answered Aug 21 '15 at 08:11

DECLARE @Tmp TABLE (Id INT,Name VARCHAR(20)) 
INSERT @Tmp SELECT 1,'182:1' 
INSERT @Tmp SELECT 2,'195:2' 
INSERT @Tmp SELECT 3,'213:1' 


--Using PARSENAME 

SELECT Id, 
       PARSENAME(REPLACE(Name,':','.'),2) Value1, 
       PARSENAME(REPLACE(Name,':','.'),1) Value2 
FROM @Tmp

This should be able to di it.. Let me know if it helps

score 0 · Answer 2 · answered Aug 21 '15 at 08:12

0

This is one method but subject to sql injection

declare @s varchar(2000),@data varchar(2000)

select @s='1:182:1'

select @data=''''+replace(@s,':',''',''')+'''' 

exec('select '+@data)

answered Aug 21 '15 at 08:12

Madhivanan

13,470
1
24
29

How to split string in multiple column in sql server

2 Answers2