Explanation of LIKE '%[0-9]%'?

Question

Explanation of LIKE '%[0-9]%'?

DataAnalyst011

SSCarpal Tunnel

Points: 4035
More actions
June 27, 2013 at 8:06 am

#295088

From time to time I need to check if a column is completely numeric (or usually, check for the row contain something other than numeric). I've read the ISNUMERIC has problems. I've used LIKE '%[0-9]%'? successfully, but even after reading around in several places I still don't understand how it works. And I don't want to use anything I can't support. Would someone mind giving me a blow by blow explanation of what each "thing" is doing here? (And if the statement needs to be improved please do so. I've seen some use a ^ in the statement before).
all digits
is a valid number anywhere in the world
is convertible to one of the available number datatypes in SQL Server

These are all different concepts.

12345 is all digits, a valid number, and convertible to most SQL Server number types.
12345.00 is NOT all digits, but is still a valid number, and convertible to DECIMAL / FLOAT / REAL.
12,345 is NOT all digits, but is still a valid number, and convertible to MONEY.
123.45E+03 is NOT all digits, but is still a valid number, and convertible to FLOAT / REAL.
12.345.678,9 is NOT all digits, is NOT convertible, but is still a valid number in some locales
12 345 678,9 is NOT all digits, is NOT convertible, but is still a valid number in some locales (such as fr-FR)
23847234872893475983479583749583749573945739 is all digits, is a valid number, but is NOT convertible to any SQL Server number types as it is larger than 38 digits

So first you need to be clear on what you will accept as being a number and what is not valid. Then you have several options, namely:

LIKE operator as you have used before: LIKE '%[^0-9]%'. This will find rows that are NOT all digits, but won't catch NULL or empty, which may or may not be acceptable so you might need additional WHERE clauses.
If you are running SQL Server 2012 (or newer at some point) use the new TRY_PARSE() function
String_IsNumeric function (free in SQL# (SQLsharp)[/url])
RegEx functions (free in SQL# (SQLsharp)[/url])

Take care,

Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 1

...here's an example of another version I've seen: NOT LIKE '%[^0-9]%'

Sean Lange SSC Guru Points: 286601 More actions · Answer 2

DataAnalyst011 (6/27/2013)
From time to time I need to check if a column is completely numeric (or usually, check for the row contain something other than numeric). I've read the ISNUMERIC has problems. I've used LIKE '%[0-9]%'? successfully, but even after reading around in several places I still don't understand how it works. And I don't want to use anything I can't support. Would someone mind giving me a blow by blow explanation of what each "thing" is doing here? (And if the statement needs to be improved please do so. I've seen some use a ^ in the statement before).

This is a regular expression. All it does is checks if the the string has any number in it (0,1,2,3,4,5,6,7,8,9).

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286601 More actions · Answer 3

DataAnalyst011 (6/27/2013)
...here's an example of another version I've seen: NOT LIKE '%[^0-9]%'

This is another regular expression. This basically is saying give me all the rows where the value is not like NOT a number. It is kind of a double negative. The first one you posted checks if there is a number anywhere in the string, this one makes sure that every single character is a number.

Here is a code example to show you what I mean.

declare @SearchVal varchar(10) = 'asdf1234asdf'

select 'yes'

where @SearchVal like '%[0-9]%'

select 'yes'

where @SearchVal NOT LIKE '%[^0-9]%'

set @SearchVal = 'asdf'

select 'yes'

where @SearchVal like '%[0-9]%'

select 'yes'

where @SearchVal NOT LIKE '%[^0-9]%'

set @SearchVal = '2345'

select 'yes'

where @SearchVal like '%[0-9]%'

select 'yes'

where @SearchVal NOT LIKE '%[^0-9]%'

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DataAnalyst011 SSCarpal Tunnel Points: 4035 More actions · Answer 4

DataAnalyst011

SSCarpal Tunnel

Points: 4035

June 27, 2013 at 10:51 am

#1628064

Thank you so much! This is very helpful.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

This should be helpful too: MSDN match expression article.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Dwain Camps SSC Guru Points: 86978 More actions · Answer 6

Sean Lange (6/27/2013)
DataAnalyst011 (6/27/2013)
From time to time I need to check if a column is completely numeric (or usually, check for the row contain something other than numeric). I've read the ISNUMERIC has problems. I've used LIKE '%[0-9]%'? successfully, but even after reading around in several places I still don't understand how it works. And I don't want to use anything I can't support. Would someone mind giving me a blow by blow explanation of what each "thing" is doing here? (And if the statement needs to be improved please do so. I've seen some use a ^ in the statement before).
This is a regular expression. All it does is checks if the the string has any number in it (0,1,2,3,4,5,6,7,8,9).

Hmmm... Technically I'd say it is Microsoft SQL's (somewhat limited) proxy for a Regular Expression. And I don't think they refer to it as that. I believe BOL always refers to it as a pattern (http://msdn.microsoft.com/en-us/library/ms179859.aspx).

The RegEx for this case would be something like: [0-9]{1,30}$ (or ^[0-9]{1,30}$ depending on whether you're doing a positive or negative test).

{1,30} specifies the overall length of the allowable characters.

There are add-ins to SQL (e.g., via CLR or the SQL Sharp library[/url]) that would allow validation by RegEx.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Sean Lange SSC Guru Points: 286601 More actions · Answer 7

dwain.c (7/2/2013)
Sean Lange (6/27/2013)
DataAnalyst011 (6/27/2013)
From time to time I need to check if a column is completely numeric (or usually, check for the row contain something other than numeric). I've read the ISNUMERIC has problems. I've used LIKE '%[0-9]%'? successfully, but even after reading around in several places I still don't understand how it works. And I don't want to use anything I can't support. Would someone mind giving me a blow by blow explanation of what each "thing" is doing here? (And if the statement needs to be improved please do so. I've seen some use a ^ in the statement before).
This is a regular expression. All it does is checks if the the string has any number in it (0,1,2,3,4,5,6,7,8,9).
Hmmm... Technically I'd say it is Microsoft SQL's (somewhat limited) proxy for a Regular Expression. And I don't think they refer to it as that. I believe BOL always refers to it as a pattern (http://msdn.microsoft.com/en-us/library/ms179859.aspx).
The RegEx for this case would be something like: [0-9]{1,30}$ (or ^[0-9]{1,30}$ depending on whether you're doing a positive or negative test).
{1,30} specifies the overall length of the allowable characters.
There are add-ins to SQL (e.g., via CLR or the SQL Sharp library[/url]) that would allow validation by RegEx.

True it is not technically a regular expression but...a regular expression checks patterns in strings. Conceptually it is pretty much the same thing. It just isn't quite as robust. It is sort of like a t-sql mini-RegEx.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 8

DataAnalyst011 (6/27/2013)
From time to time I need to check if a column is completely numeric (or usually, check for the row contain something other than numeric). I've read the ISNUMERIC has problems. I've used LIKE '%[0-9]%'? successfully, but even after reading around in several places I still don't understand how it works.
...here's an example of another version I've seen: NOT LIKE '%[^0-9]%'

Hi there. First things first: please define "numeric" and what you are REALLY checking for. Are you looking for:

Jeff Moden SSC Guru Points: 1004553 More actions · Answer 9

DataAnalyst011 (6/27/2013)
From time to time I need to check if a column is completely numeric (or usually, check for the row contain something other than numeric). I've read the ISNUMERIC has problems. I've used LIKE '%[0-9]%'? successfully, but even after reading around in several places I still don't understand how it works. And I don't want to use anything I can't support. Would someone mind giving me a blow by blow explanation of what each "thing" is doing here? (And if the statement needs to be improved please do so. I've seen some use a ^ in the statement before).

Based on the word "ISNUMERIC" and the description above, I believe you're looking for an "IsAllDigits" solution. The following article will help with that.

http://www.sqlservercentral.com/articles/IsNumeric/71512/

I also recommend that you lookup "LIKE" in Books Online (the "help" system that comes with SQL Server). There will also be a popup when you search for "LIKE" in Books Online with the title "Pattern Matching in Search Conditions". That would be very good to study, as well.

--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)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

Solomon Rutzky (7/27/2013)
23847234872893475983479583749583749573945739 is all digits, is a valid number, but is NOT convertible to any SQL Server number types as it is larger than 38 digits
...

You can convert it to float (with lose of some precision ;-))

select cast('23847234872893475983479583749583749573945739' as float)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004553 More actions · Answer 11

Eugene Elutin (7/29/2013)
Solomon Rutzky (7/27/2013)
23847234872893475983479583749583749573945739 is all digits, is a valid number, but is NOT convertible to any SQL Server number types as it is larger than 38 digits
...
You can convert it to float (with lose of some precision ;-))
select cast('23847234872893475983479583749583749573945739' as float)

Considering that FLOAT only has 15 digits of precision, it'll be a pretty big loss.

--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)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

Jeff Moden (7/29/2013)
Eugene Elutin (7/29/2013)
Solomon Rutzky (7/27/2013)
23847234872893475983479583749583749573945739 is all digits, is a valid number, but is NOT convertible to any SQL Server number types as it is larger than 38 digits
...
You can convert it to float (with lose of some precision ;-))
select cast('23847234872893475983479583749583749573945739' as float)
Considering that FLOAT only has 15 digits of precision, it'll be a pretty big loss.

I wouldn't call 1.32% a such "big loss" :hehe:

However you are right! It does depend! If this loss constitutes my interest in £ - I would probably die from heart-attack

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jeff Moden SSC Guru Points: 1004553 More actions · Answer 13

Eugene Elutin (7/29/2013)
Jeff Moden (7/29/2013)
Eugene Elutin (7/29/2013)
Solomon Rutzky (7/27/2013)
23847234872893475983479583749583749573945739 is all digits, is a valid number, but is NOT convertible to any SQL Server number types as it is larger than 38 digits
...
You can convert it to float (with lose of some precision ;-))
select cast('23847234872893475983479583749583749573945739' as float)
Considering that FLOAT only has 15 digits of precision, it'll be a pretty big loss.
I wouldn't call 1.32% a such "big loss" :hehe:
However you are right! It does depend! If this loss constitutes my interest in £ - I would probably die from heart-attack

Heh... absolutely agreed but wasn't talking about the loss in "value" of the number. Was talking about the number of digits that would be lost when trying to determine if a long string could be checked for "IsAllDigits".

--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)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 14

Jeff Moden (7/29/2013)
Eugene Elutin (7/29/2013)
Jeff Moden (7/29/2013)
Eugene Elutin (7/29/2013)
Solomon Rutzky (7/27/2013)
23847234872893475983479583749583749573945739 is all digits, is a valid number, but is NOT convertible to any SQL Server number types as it is larger than 38 digits
...
You can convert it to float (with lose of some precision ;-))
select cast('23847234872893475983479583749583749573945739' as float)
Considering that FLOAT only has 15 digits of precision, it'll be a pretty big loss.
I wouldn't call 1.32% a such "big loss" :hehe:
However you are right! It does depend! If this loss constitutes my interest in £ - I would probably die from heart-attack
Heh... absolutely agreed but wasn't talking about the loss in "value" of the number. Was talking about the number of digits that would be lost when trying to determine if a long string could be checked for "IsAllDigits".

Hmm. I thought I had tested that one using CONVERT and that it errored, but I tried again and it worked. Thanks for mentioning that.

I agree that the loss of precision (i.e. rounding up) is non-ideal but seems to happen with the decimal types: MONEY, SMALLMONEY, DECIMAL / NUMERIC. So not a true conversion in the sense of being able to convert it back to the exact same string, but technically it does fit into the datatype. So it still fits into what I was saying regarding the need to determine if the value expressed in the string is really a number with respect to the end purpose of that number.

Take care,

Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Explanation of LIKE '%[0-9]%'?

Cookies on SQLServerCentral