0

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.

charan tej
  • 1,054
  • 10
  • 29
LCaraway
  • 1,257
  • 3
  • 20
  • 48

2 Answers2

3

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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

Zorkolot
  • 1,899
  • 1
  • 11
  • 8