CODEWITHSUNDEEP
sqlsql-server
shinzie
shinzie

Reputation: 3

Data type varchar to numeric

I have a table with Varchar values that are numbers, and I want them to be decimal values. I'm getting an error saying Error converting data type varchar to numeric. I've tried:

SELECT ROUND(CAST(MYCOLUMN AS decimal(10, 2)), 2)

FROM TABLE

and 

ALTER TABLE MYTABLE


ALTER  COLUMN MYCOLUMN DECIMAL(10,2)

and

ALTER TABLE MYTABLE


MODIFY COLUMN ACQ_FIELD_8 DECIMAL(10,2)

and

SELECT ISNULL(CAST(NULLIF(MYCOLUMN, 'NULL') AS NUMERIC(10,2)), 0)

FROM MYTABLE

and I keep getting the same error. I looked the through the data to look for any special characters or letters by using: 

SELECT MYCOLUMN FROM MYTABLE

WHERE MYCOLUMN LIKE '%[a-zA-Z]%'

or MYCOLUMN LIKE '%[(]%'  --etc for each character.

The only thing that I have found is that every value for some reason has a '^' in it, but when I replace the '6' with nothing, the value still can't be converted to a decimal or numeric. If I try ordering the column by number (< 0.00), I get Arithmetic overflow error converting varchar to data type numeric.

Anyone know what to do?

Upvotes: 0

Views: 6745

Answers (2)

PollusB
PollusB

Reputation: 1816

You can try isnumeric:

SELECT ROUND(CAST(MYCOLUMN AS decimal(10, 2)), 2)
FROM TABLE
WHERE isnumeric(MYCOLUMN) = 1

Upvotes: 1

blogbydev
blogbydev

Reputation: 1485

Try this : 

select round(try_convert(decimal(10,2), Mycolumn),2)

looks like you have some values which can not be converted to decimal. try_convert will convert them as NULL

Upvotes: 1

Related Questions