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

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 wa开发者_运维技巧y of doing it? I am using MSSQL Server 2005.

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

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 delimiter and returns either the first or second value.

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

You can use something like this:

SELECT SUBSTRING_INDEX(field, ',', 1)

Note: Its not the efficient way of doing things in rdbms. Consider normalizing your Database.