SQL Statement -- Getting the correct substring

[Stomper622]

Hey all.
I have a question. I have a column ('Value') in a table ('Data'). The column is of type varchar(500). The contents of this table look like this:

57.24,0.00,1029.23
400.25,0.00,612.12
etc.

Basically: value - comma - value - comma - value

How do I get the middle value, ie the 0.00 by using a select statement?

Any help would be greatly appreciated.

Chris

[rudgej]

If you needed all of the data then just use:

SELECT * FROM DATA

and get the relevent column from the returned recordset:

rs(0) would be 57.24
rs(1) would be 0.00

Of course you could just use the field name to get only the 0.00 field:

SELECT FIELDNAME FROM DATA

rs(0) would be 0.00

[vb_dba]

Okay, as long as your field only has three comma seperated values in it and you are using SQL Server, this should work:

Select Left(Substring(Value, Charindex(',', Value) + 2, Len(Value) - Charindex(',', Value) - 2),
Charindex(',', Substring(Value, Charindex(',', Value) + 2, Len(Value) - Charindex(',', Value) - 2)) - 1)
From Data

Hope This Helps,

Chris

[Stomper622]

Chris,
The field is not always going to be 3 comman separated values.

PS, that statement doesn't work.


Thanks,
Chris

[vb_dba]

I changed the statement so that it works now.

How do you determine what the middle number is if there are an odd number of numbers, ie) 1.11, 0.00, 3.33, 2.22? Can you perform this action in a stored procedure?

Chris

[Stomper622]

Chris,
My goal is to always get the second value in the comma separated string.

Anyways, I came to the solution myself.

select Answer= case when charindex(',', substring(value, charindex(',', value)+1, len(value)))<> 0
then
cast (substring(substring(value, charindex(',', value)+1, len(value)),1, charindex(',', substring(value, charindex(',', value)+1, len(value)))-1) as money)
else
cast (substring(value, charindex(',', value)+1, len(value)) as money)
end


Thanks for your help though!
Chris