Checking varchar data to see if it can be cast as decimal

Question

Post reply

Checking varchar data to see if it can be cast as decimal

tarr94

SSCommitted

Points: 1684
More actions
March 19, 2014 at 2:22 pm

#396448

Hi,
I have a varchar field. Most of the values in this field are numeric, and we need to update these to format them to display five decimal places.
If I simply try casting the values as decimals, then casting them back into varchar, I get an arithmetic overflow error. I assume this is because not all of the values in this field can be cast as decimals. Some of them contain nonnumeric characters.
Any ideas on how I can query the table to find the values that can't be converted to decimal would be greatly appreciated!
Thank you.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

ISNUMERIC should get you most of the way there, but it will return 1 if it can be converted to any of these data types:

int

numeric

bigint

money

smallint

smallmoney

tinyint

float

decimal

real

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You could try something like this:

WHERE somecolumn NOT LIKE '%[^0-9.]%'

This won't be completely safe as it will return false positives for values with more than one point(.).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tarr94 SSCommitted Points: 1684 More actions · Answer 3

Thanks for responding, Adam and Luis.

Unfortunately, after trying both of these methods, I still receive an error, "Error converting data type varchar to numeric". My update statement looks something like this:

UPDATE MyTable

SET MyField = CAST(CAST(MyField AS DECIMAL(10,5)) AS VARCHAR)

FROM MyTable

WHERE ISNUMERIC(MyField) = 1

AND MyField LIKE '%[^0-9.]%'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SQL Server will apply the filter and the conversion in whichever order results in the lowest cost - and in this case, the order chosen is breaking your query. Here's one way to ensure that they occur in the correct order:

DROP TABLE #MyTable

CREATE TABLE #MyTable (MyField VARCHAR(10))

INSERT INTO #MyTable VALUES ('100'),('101'),('102'),('103'),('10A'),('105'),('10B')

UPDATE t

SET MyField = x.MyField

FROM #MyTable t

CROSS APPLY (

SELECT MyField = CASE

WHEN MyField NOT LIKE '%[^0-9.]%' THEN CAST(CAST(t.MyField AS DECIMAL(10,5)) AS VARCHAR)

ELSE MyField END

) x

WHERE t.MyField NOT LIKE '%[^0-9.]%'

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tarr94 SSCommitted Points: 1684 More actions · Answer 5

Chris,

I ran your query and it added the decimals where it should for the data you created.

But for whatever reason, I'm getting an arithmetic overflow error now when I try doing this with my data. I do notice some values include very large integers, or "$" signs, but there are many records, and I'm not sure how else to identify which ones aren't going to format correctly.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Can you augment the sample table in my last post to account for your observations? Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

tarr94 SSCommitted Points: 1684 More actions · Answer 7

Thank you for your help everyone. I think I found the issue.

There some some numerical values that were greater than ten digits. I was trying to cast them into decimal (10,5). Once I filtered these out, the update ran fine.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 8

Luis Cazares (3/19/2014)
You could try something like this:
WHERE somecolumn NOT LIKE '%[^0-9.]%'
This won't be completely safe as it will return false positives for values with more than one point(.).

WHERE

somecolumn NOT LIKE '%[^0-9.]%' AND

somecolumn NOT LIKE '%.%.%'

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".