locked
LIKE '%[^0-9]%' behaviour RRS feed

  • Question

  • Hi there,

                consider the following code:

    declare @string VARCHAR(8000)

    set @string = '374907403001005'

    if @string like '%[^0-9]%'
     select 'yes'
    else
     select 'no'

    On my laptop, this code returns 'no' as expected, but on a customer server, it returns 'yes'.  I have pored over the server and database settings on both servers and they appear to be identical.  Both are 2008 R2 64 bit. What would cause the behaviour to differ?

    Friday, October 4, 2013 3:13 PM

Answers

  • Hi,

    Check if input string has dots or any special characters

    declare @string VARCHAR(8000)
    set @string = '374907403001005.'
    if @string like '%[^0-9]%'
     select 'yes'
    else
     select 'no'


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    • Edited by SathyanarrayananS Friday, October 4, 2013 3:19 PM typo
    • Marked as answer by Waldy Friday, October 4, 2013 3:34 PM
    Friday, October 4, 2013 3:18 PM

All replies

  • You run the exact code on both computers, right? 


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, October 4, 2013 3:18 PM
  • Hi,

    Check if input string has dots or any special characters

    declare @string VARCHAR(8000)
    set @string = '374907403001005.'
    if @string like '%[^0-9]%'
     select 'yes'
    else
     select 'no'


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    • Edited by SathyanarrayananS Friday, October 4, 2013 3:19 PM typo
    • Marked as answer by Waldy Friday, October 4, 2013 3:34 PM
    Friday, October 4, 2013 3:18 PM
  • Assuming that you are running this against a column on the server.  Is the column datatype char or nchar?  If so, then the column is padded with spaces and thus you will get yes.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    • Proposed as answer by Naomi N Friday, October 4, 2013 3:31 PM
    Friday, October 4, 2013 3:24 PM
  • Most likely this is a problem where someone replaced the zero with the letter O. 
    Friday, October 4, 2013 3:32 PM
  • Do you run the exact same code or something different? Also, what is the SQL Server collation and database collation?

    Also, are you running latest SP on both servers?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, October 4, 2013 3:33 PM
  • No, you hit the nail on the head.  That code does work.  When I was debugging, it appeared as though that number was the value passed, but for whatever reason, when I checked the value in the text visualizer, there was a carriage return on the end. 
    Friday, October 4, 2013 3:36 PM
  • Hi,

    You are using "like" as @string like '%[^0-9]%' which means any string that contains numbers 0 to 9 but not beginning with numbers.

    % means any string of zero/any no's of characters.

    ^ means any one character but not in the range

    so '%[^0-9]%' means string should be start with any character but not start with numeric value.

    Solution:  Replace '%[^0-9]%' with '%[0-9]%' if your string is like '374907403001005'



    Friday, October 4, 2013 3:39 PM