Get first or second values from a comma separated value in SQL

Question

I have a column that stores data like (42,12). Now I want to fetch 42 or 12 (two different select queries). I have searched and found some similar but much more complex scenarios. Is there any easy way of doing it? I am using MSSQL Server 2005.

Given there will always be only two values and they will be integer

Answer 1

You can try something like this

DECLARE @Table TABLE(
     Val VARCHAR(50)
)

INSERT INTO @Table (Val) SELECT '42,12'

SELECT  *,
     CAST(LEFT(Val,CHARINDEX(',',Val)-1) AS INT) FirstValue,
     CAST(RIGHT(Val,LEN(Val) - CHARINDEX(',',Val)) AS INT) SecondValue
FROM    @Table

Answer 2

I will help you here by telling you that the reason you have this problem is because the database (which you may not have any control over), violates first normal form. Among other things, first normal form says that each column should hold a single value, not multiple values. This is bad design.

Now, having said this, the first solution that pops into my head is to write a UDF that parses the value in this column, based on the delimeter and returns either the first or second value.

Randy