Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Explanation of LIKE '%[0-9]%'? Expand / Collapse
Author
Message
Posted Thursday, June 27, 2013 8:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 3:36 PM
Points: 92, Visits: 338
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).
Post #1468169
Posted Thursday, June 27, 2013 8:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 3:36 PM
Points: 92, Visits: 338
...here's an example of another version I've seen: NOT LIKE '%[^0-9]%'
Post #1468175
Posted Thursday, June 27, 2013 8:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
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 Moden's 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)
Post #1468190
Posted Thursday, June 27, 2013 8:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
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 Moden's 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)
Post #1468194
Posted Thursday, June 27, 2013 10:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 10, 2014 3:36 PM
Points: 92, Visits: 338
Thank you so much! This is very helpful.
Post #1468262
Posted Friday, June 28, 2013 1:14 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 7:34 PM
Points: 555, Visits: 2,580
This should be helpful too: MSDN match expression article.

-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1468688
Posted Tuesday, July 2, 2013 12:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 6:01 PM
Points: 3,609, Visits: 5,222
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!
Post #1469314
Posted Tuesday, July 2, 2013 7:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
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.


_______________________________________________________________

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 Moden's 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)
Post #1469482
Posted Saturday, July 27, 2013 5:17 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 3:30 PM
Points: 352, Visits: 1,868
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/
    Post #1478322
    Posted Saturday, July 27, 2013 6:12 PM


    SSC-Dedicated

    SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

    Group: General Forum Members
    Last Login: Today @ 12:18 AM
    Points: 36,751, Visits: 31,202
    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."

    (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

    Helpful Links:
    How to post code problems
    How to post performance problems
    Post #1478326
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse