SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
DataAnalyst011
DataAnalyst011
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 449
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).
DataAnalyst011
DataAnalyst011
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 449
...here's an example of another version I've seen: NOT LIKE '%[^0-9]%'
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62331 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62331 Visits: 17954
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
DataAnalyst011
DataAnalyst011
SSC Eights!
SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)SSC Eights! (831 reputation)

Group: General Forum Members
Points: 831 Visits: 449
Thank you so much! This is very helpful.
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13342 Visits: 8001
This should be helpful too: MSDN match expression article.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17729 Visits: 6431
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) that would allow validation by RegEx.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62331 Visits: 17954
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) 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. :-P

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Solomon Rutzky
Solomon Rutzky
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3330 Visits: 3029
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:

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

  • RegEx functions (free in SQL# (SQLsharp))


  • Take care,
    Solomon...

    SQL# - http://www.SQLsharp.com/
    Jeff Moden
    Jeff Moden
    SSC Guru
    SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

    Group: General Forum Members
    Points: 214204 Visits: 41979
    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.
    If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search