All geek questions in one place

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

I have a table in which there is a column that has a product value, but some (very few) rows have text mixed in the column.

Is it possible to create a new decimal type column and fill it with another column and just ignore rows with “bad” data in it?

I know I can do this with code, but was hoping I could do it with an SQL query in some way?

+3
source share
3 answers

You can use case to distinguish only whole columns. This will return NULL for non-numeric columns:

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

:

select *
from YourTable
where isnumeric(col1) = 1
+5

, , .

1:

(9, 2) (PriceOfItem) (ItemPrices)

2. ( )

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

How does this sound? I use it all the time. Simple and fast.

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

Source: https://habr.com/ru/post/1736147/

More articles:


All Articles