CODEWITHSUNDEEP
t-sqlsplitmultiple-columns
AGx-07_162
AGx-07_162

Reputation: 301

Split Data in one column to multiple Columns

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?

Upvotes: 0

Views: 1609

Answers (1)

Kaf
Kaf

Reputation: 33839

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

Upvotes: 0

Related Questions