CODEWITHSUNDEEP
sqlsql-server
abs786123
abs786123

Reputation: 609

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

Upvotes: 1

Views: 341

Answers (4)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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))

Upvotes: 1

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Try with the below code.. you have to mention the precision.

    DECLARE @a nvarchar(50)='0.8617 x SOQ ^ -0.2155' 

    SELECT cast(substring(@a,1,7) as decimal(15,4)),cast(substring(@a,16,22) as decimal(15,4))

OR 

   SELECT cast(substring(@a,1,7) as numeric(15,4)),cast(substring(@a,16,22) as numeric(15,4))

And if you want to check ,whether the column returns a numeric value, debug with the following code.

SELECT Capacity_Band3
FROM YourTable
WHERE ISNUMERIC(substring(Capacity_Band3,1,7)) !=1 
       OR ISNUMERIC(substring(Capacity_Band3,16,22))!=1

Upvotes: 2

Paweł Dyl
Paweł Dyl

Reputation: 9143

Are you sure your text have no special/additional symbols? Take a look at this query:

DECLARE @s nvarchar(100) = '0.8617 x SOQ ^ -0.2155'

SELECT
    cast(substring(@s,1,7) as numeric(19,4)),
    cast(substring(@s,16,22)as numeric(19,4))

Precision is required but won't produce errors. If it is part of bigger query make sure all records have this format. Try wrapping source string with LTRIM/RTRIM, trim SUBSTRING result as well.

For debugging purposes you can use:

SELECT
    substring(Capacity_Band3,1,7),
    substring(Capacity_Band3,16,22)
FROM YourTable

Make sure all records are valid numbers.

Upvotes: 2

dean
dean

Reputation: 10098

You got the parameters for the substring wrong. Also, a numeric without precision is same as an int.

 declare @Capacity_Band3 varchar(30) = '0.8617 x SOQ ^ -0.2155' 

 select cast(substring(@Capacity_Band3,1,7) as numeric(10,4)),
 cast(substring(@Capacity_Band3,16,8)as numeric(10,4))

Upvotes: 1

Related Questions