Split Data in one column to multiple Columns

Question

I have a column in a table that contains numeric data separated by a hyphen. I need to split this data into three columns so each part of this numeric value is in a separate column. The specific data is for learning purposes but I've also seen databases where name fields are one column instead of three (e.g. "FirstMiddleLast" instead of "First", "Middle", Last").

Here is a sample of the numeric value:

1234-56-78

I would like to split that so I have three columns

1234 | 56 | 78

How can I achieve this?

try google and you can find this: http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql — Simon Wang, Jan 15 '13 at 16:01
Three columns, as stated. I only listed it that way in the example because I'm not sure how to use the tools here to create tables and such for my questions but it would be Col1 (1234) Col2(56) and Col3(78). I hope that helps. — AGx-07_162, Jan 15 '13 at 17:03

score 0 · Answer 1 · answered Jan 15 '13 at 17:21

Try this (Sql Fiddle here);

declare @s varchar(50)='1234-56-78'

select left(@s,charindex('-',@s,1)-1) Col1,
       substring(@s,charindex('-',@s,1)+1, len(@s)-charindex('-',reverse(@s),1)-
       charindex('-',@s,1)) Col2,
       right(@s,charindex('-',reverse(@s),1)-1) Col3

--results
Col1    Col2    Col3
1234    56      78

Split Data in one column to multiple Columns

1 Answers1