Help me with the regular expression

  • Good Day!
    I am looking for a way to test a string to make sure it only has numbers  and/or a decimal point ( Not more than one decimal point ) 
    So what I  am really looking for is a way to test for decimal number ( and integers ) 
    Any idea ?


    declare @SearchVal varchar(10) = '1234.00'

    select 'yes'
    where
    @SearchVal like '%[0-9][.][0-9]%'

  • WHERE @SearchVal NOT LIKE '%[^0-9.]%'
    AND LEN(@SearchVal) - LEN(REPLACE(@SearchVal,'.','')) < 2

    "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

  • declare @SearchVal varchar(10) = '1234.00'

    select 'yes'
    where @SearchVal NOT LIKE '%[^0-9.]%'
        AND @SearchVal NOT LIKE '%.%.%'

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Another option is to use TRY_CONVERT() if you want to make sure it'll actually convert to something in SQL server 

    select 'yes'
    where TRY_CONVERT(decimal(29, 9), @SearchVal) IS NOT NULL

  • ZZartin - Wednesday, February 28, 2018 12:33 PM

    Another option is to use TRY_CONVERT() if you want to make sure it'll actually convert to something in SQL server 

    select 'yes'
    where TRY_CONVERT(decimal(29, 9), @SearchVal) IS NOT NULL

    Good solution and would probably be fine. Note this though: 

    SELECT TRY_CONVERT(decimal(29, 9), $)

    "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

  • Alan.B - Wednesday, February 28, 2018 12:42 PM

    ZZartin - Wednesday, February 28, 2018 12:33 PM

    Another option is to use TRY_CONVERT() if you want to make sure it'll actually convert to something in SQL server 

    select 'yes'
    where TRY_CONVERT(decimal(29, 9), @SearchVal) IS NOT NULL

    Good solution and would probably be fine. Note this though: 

    SELECT TRY_CONVERT(decimal(29, 9), $)

    The pattern that he is trying to match excludes strings that represent negative numbers, which the TRY_CONVERT will not.  Of course, it's easy to modify the WHERE clause to only return positive (or non-negative numbers) or to modify the pattern to match negative numbers.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Wednesday, February 28, 2018 12:52 PM

    Alan.B - Wednesday, February 28, 2018 12:42 PM

    ZZartin - Wednesday, February 28, 2018 12:33 PM

    Another option is to use TRY_CONVERT() if you want to make sure it'll actually convert to something in SQL server 

    select 'yes'
    where TRY_CONVERT(decimal(29, 9), @SearchVal) IS NOT NULL

    Good solution and would probably be fine. Note this though: 

    SELECT TRY_CONVERT(decimal(29, 9), $)

    The pattern that he is trying to match excludes strings that represent negative numbers, which the TRY_CONVERT will not.  Of course, it's easy to modify the WHERE clause to only return positive (or non-negative numbers) or to modify the pattern to match negative numbers.

    Drew

    Agreed.

    I think your solution is the best. My code:
    LEN(@SearchVal) - LEN(REPLACE(@SearchVal,'.',''))
    is the fastest way I know to count individual characters but it's not necessary here as we're only trying to ensure that there are not two dots which is more efficiently handled with your code:
    @SearchVal NOT LIKE '%.%.%'

    "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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply