Using "not like" to filter out non-numeric values from varchar field

Question

Post reply

Using "not like" to filter out non-numeric values from varchar field

miles_lesperance

SSC Eights!

Points: 805
More actions
March 26, 2014 at 10:20 am

#396483

I am putting a SELECT statement together where I need to evaluate a results field, to determine how the color indicator will show on a SSRS report. I am running into a problem when I try to filter out any non-numeric values from a varchar field, using a nested CASE statement.
For example, this results field may contain values of '<1', '>=1', '1', '100', '500', '5000', etc. For one type of test, I need a value of 500 or less to be shown as a green indicator in a report, and any value over that would be flagged as a red. Another test might only allow a value of 10 or less before being flagged with a red.
This is why I setup a CASE statement for an IndicatorValue that will pass over to the report to determine the indicator color. Using CASE statements for this is easier to work with, and less taxing on the report server, if done in SQL Server instead of nested SSRS expressions, especially since a variety of tests have different result values that would be flagged as green or red.
I have a separate nested CASE statement that will handle any of the values that contain ">" or "<", so I am using the following to filter those out, and then convert it to an int value, to determine what the indicator value should be. Here is the line of the script that is erring out"
case when (RESULT not like '%<%') or (RESULT not like '%>%') then
CASE WHEN (CONVERT(int, RESULT) between 0 and 500) THEN '2'
ELSE '0'
The message I am getting is: Conversion failed when converting the varchar value '<1' to data type int.
I thought a "not like" statement would not include those values for converting to an int, but that does not seem to be working correctly. I did also try moving the not to show as "not RESULT like", and that did not change the message.
Does anyone have any suggestions on how I can filter out non-numeric values before converting the rest of the varchar field (RESULT) to int, so that it is only converting actual numbers?

Viewing 7 posts - 1 through 6 (of 6 total)

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

dbalmf SSCrazy Points: 2063 More actions · Answer 1

Hi There

ISNUMERIC may help with this..

http://technet.microsoft.com/en-us/library/ms186272(v=sql.105).aspx

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

miles_lesperance SSC Eights! Points: 805 More actions · Answer 2

That worked. I changed my CASE statement to look like the following:

case when ISNUMERIC(SRESULT) = 1 then

CASE WHEN CONVERT(int, SRESULT) between 0 and 500 THEN '2' --Green

ELSE '0'

END

It is now only trying to convert those values that are only numeric. Thank you for the suggestion!!:-)

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

Be careful when using ISNUMERIC, it can give you unexpected results.

http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

miles_lesperance (3/26/2014)
That worked. I changed my CASE statement to look like the following:
case when ISNUMERIC(SRESULT) = 1 then
CASE WHEN CONVERT(int, SRESULT) between 0 and 500 THEN '2' --Green
ELSE '0'
END
It is now only trying to convert those values that are only numeric. Thank you for the suggestion!!:-)

Be VERY aware that the definition of what is "numeric" encompasses a whole world of characters that you never imagined and that ISNUMERIC will allow many strange renditions of what "numeric" actually means that will blow up in your face. For example, what will ISNUMERIC return for the following?

SELECT ISNUMERIC(',,,,,'), ISNUMERIC('1E3'), ISNUMERIC('1D3'), ISNUMERIC('$')

I guarantee that each of those will cause a failure if you try to convert them to INT.

Please see the article that Luiz pointed you to. Don't ever use ISNUMERIC for an "IsAllDigits" function because it's not.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dbalmf SSCrazy Points: 2063 More actions · Answer 5

They Say you learn something new every day...

I''m now off to change a couple of scripts which us ISNUMERIC to capture where I'm filtering numbers..

Thanks for pointing this out everyone.. :hehe:

============================================================
David

http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 6

(RESULT not like '%<%') or (RESULT not like '%>%')

will pass everything except NULLs to the convert as anything excluded by the first clause will be included by the second and vice versa - is this really what you want or are you after.

NOT ((RESULT like '%<%') or (RESULT like '%>%'))