abs786123 abs786123 - 1 year ago 81
SQL Question

Casting issues in SQL server

I have a column name Capacity_Band3 with the value as follows
'0.8617 x SOQ ^ -0.2155' which is a string.
I am trying to extract the first and last bit in this example 0.8617 by using

cast(substring(Capacity_Band3,1,7) as numeric))

but the conversion fails

I am trying to cast it as a numeric value so i can do calculation, what am I doing wrong?

I would also need to do this with the last bit

cast(substring(Capacity_Band3,16,22)as numeric))

Thank you

Answer Source

USE LEFT and Right Function to extract number

SELECT LEFT(ColumnName,6),RIGHT(ColumnName,6)

THEN Apply Cast Operation

SELECT CAST(LEFT(ColumnName,6) AS DECIMAL(18,2)),CAST(RIGHT(ColumnName,6) AS DECIMAL(18,2))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download