CODEWITHSUNDEEP
castingsubstringcharindex
BachPhi
BachPhi

Reputation: 180

SQL Extract & Convert string to a number then use it for calculation to generate a third column

I have:

col1(varchar), col2(float)
25-PACK      , 4

want to have:

col1 , col2, col3
25   , 4   , 100

This is what I have:

CAST(SUBSTRING(col1, CHARINDEX('-P',col1)-2 ,2) AS FLOAT) AS NewCol1, Col2, Col2*NewCol1 AS Col3

The issue here is NewCol1 is an invalid name. How do I resolve this issue? TIA.

Upvotes: 0

Views: 28

Answers (1)

BachPhi
BachPhi

Reputation: 180

I got it:

CAST(SUBSTRING(col1, CHARINDEX('-P',col1)-2 ,2) AS FLOAT) AS NewCol1, Col2, Col2*CAST(SUBSTRING(col1, CHARINDEX('-P',col1)-2 ,2) AS FLOAT) AS Col3

Upvotes: 0

Related Questions