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 Monday, July 29, 2013 6:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:50 AM
Points: 2,856, Visits: 5,124
[b]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!"
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help
    Post #1478547
    Posted Monday, July 29, 2013 7:23 AM


    SSC-Dedicated

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

    Group: General Forum Members
    Last Login: Yesterday @ 8:14 PM
    Points: 35,215, Visits: 31,665
    Eugene Elutin (7/29/2013)
    [b]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."

    (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 #1478578
    Posted Monday, July 29, 2013 8:18 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Yesterday @ 5:50 AM
    Points: 2,856, Visits: 5,124
    Jeff Moden (7/29/2013)
    Eugene Elutin (7/29/2013)
    [b]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"
    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!"
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help
    Post #1478611
    Posted Monday, July 29, 2013 8:53 AM


    SSC-Dedicated

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

    Group: General Forum Members
    Last Login: Yesterday @ 8:14 PM
    Points: 35,215, Visits: 31,665
    Eugene Elutin (7/29/2013)
    Jeff Moden (7/29/2013)
    Eugene Elutin (7/29/2013)
    [b]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"
    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."

    (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 #1478631
    Posted Monday, July 29, 2013 9:47 AM


    Old Hand

    Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

    Group: General Forum Members
    Last Login: Tuesday, September 16, 2014 1:54 PM
    Points: 368, Visits: 1,940
    Jeff Moden (7/29/2013)
    Eugene Elutin (7/29/2013)
    Jeff Moden (7/29/2013)
    Eugene Elutin (7/29/2013)
    [b]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"
    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# - http://www.SQLsharp.com/
    Post #1478654
    « Prev Topic | Next Topic »

    Add to briefcase ««12

    Permissions Expand / Collapse