SQL SERVER - Split a Comma Separated Number Into Two Columns

Question

I have a Column A that contains integers. In some cases, the column will contain a comma-separated list of two integers represented as a string.

Example: 1,2 . In this situation I need to do two things.

1. Identify if data has comma or not. If not populate value into Column B.
2. If it does have a comma, it needs to populate the first number into Column C and the Second into Column B

If it helps to understand the problem better, we have a data vendor that has updated an ID field in their data. Their idea of maintaining the legacy ID is to simply list it followed by the new ID separated by a comma. Why they choose to do this instead of creating new columns I have no idea. I am sure they have their reasons.

Answer 1

You can do this as:

select cast( (case when a like '%,%' then substring(a, charindex(',', a) + 1, len(a)) else a end) as int) b,
       cast( (case when a like '%,%' then left(a, charindex(',', a) - 1) end) as int) c

Of course, the cast() is unnecessary if you want the value to remain a string.

Answer 2

You can use a combination of RIGHT, LEFT and Charindex.

DECLARE @temp TABLE (val varchar(10))
INSERT INTO @temp VALUES ('5'), ('2,3'), ('0,40'), ('99,21'), ('100,25')

SELECT val [A]
      ,CASE WHEN val LIKE '%,%' THEN RIGHT(val, Charindex(',', REVERSE(val)) - 1)
            ELSE val 
        END [B]
      ,CASE WHEN val LIKE '%,%' THEN LEFT(val, Charindex(',', val) -1 )
        END [C]
  FROM @temp

I've used Shashra's question, answered by Martin Smith- for a somewhat related, but different problem here