Column has a number in it, very few rows have text mixed in, can I create a new number column?

Question

I have a table that has a column that has the cost of a product, but some (very few) rows have text mixed in the column.

Would it be possible to create a new column, of a decimal type, and populate it with the other column and just ignore the rows with the 'bad' data in it?

I know I can do this via code, but was hoping I could do it with a sql query some how?

Answer 1

You can use a case to cast only the integer columns. This would return NULL for non-numeric columns:

select 
    case when isnumeric(col1) = 1 then cast(col1 as int) end
from YourTable

Or you can just ignore the non-numeric columns:

select *
from YourTable
where isnumeric(col1) = 1

Answer 2

How does this sound? I use it all the time. Simple & quick.

NewColumn: iif(
               isnumeric([OldColumn]),
               cdbl([OldColumn])
           )

Answer 3

As another example, you could use the PATINDEX, to check for Characters. If the value returned is greater that 0, then a character is present. The number will also tell you where the first character was detected in the column.

e.g.

DECLARE @String AS VARCHAR(100)
SET @String = '1234456'

SELECT PATINDEX('%[A-Z]%',@String)

Will return 0, which is ok

DECLARE @String AS VARCHAR(100)
SET @String = '1234frg456'

SELECT PATINDEX('%[A-Z]%',@String)

Will return 5, which is the position of the first character found.

I expect you can apply this in the WHERE clause, in part of an UPDATE for your new column.

e.g Apolgies if syntax is wrong

UPDATE <TABLE>
SET NewColumn = OldColumn
WHERE PATINDEX('%[A-Z]%', OldColumn) = 0

Answer 4

Yes what you are asking is possible to do.

Step 1: Add a new column

Add a decimal (9, 2) column (PriceOfItem) to your table (ItemPrices)

Step 2: Run this query (change object names as required)

Update ItemPrices
Set PriceOfItem = Convert (Decimal (9, 2), PriceText)
Where 1=1
AND IsNumeric (PriceText) = 1