Post reply

RegexMatch Help

  • March 22, 2010 at 8:45 am

    #216898

    Hi All

    I am using RegexMatch for the first time, i need to check if there are any numeric value on a given string,

    Create table #temp ( text varchar(max))

    Insert into #temp values ('Test 123')

    Insert into #temp values ('12 test')

    Insert into #temp values ('1235')

    Insert into #temp values ('Test')

    if i am using

    SELECT dbo.RegexMatch('^([A-Za-z]|\s){1,1}([0-9]|\s){2,2}([0-9]|\s){3,3}',' text') from #temp

    this above function checks for a Digit on the first character and a number or space on 2nd and 3rd character of the string

    is there any way i can use a expression which calculates if there are any numerics on the given string, as on above example, i must get 1 for first 3 values on temp table and 0 for the last one

    Thanks in advance

    Cheers

  • March 22, 2010 at 9:33 am

    #1137648

    Well, this doesn't use RegEx (just plain ole T-SQL), but it accomplishes what you're wanting:

    select * from #temp where [text] like '%[0-9]%'

    Since you want to return a 1/0 for the match, this will do that:

    ;with CTE([Text], TextHasNum) AS

    (

    select [Text],0 from #temp

    EXCEPT

    select [Text],0 from #temp where [text] like '%[0-9]%'

    UNION

    select [Text], 1 from #temp where [text] like '%[0-9]%'

    )

    select * from CTE

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 22, 2010 at 9:36 am

    #1137653

    Thanks Wayne, this works, but got one more doubt, is there any other way to get numbers between 1 and 100 in the given string ?

  • March 22, 2010 at 10:13 am

    #1137668

    CrazyMan (3/22/2010)

    Thanks Wayne, this works, but got one more doubt, is there any other way to get numbers between 1 and 100 in the given string ?

    I don't quite understand what you're asking for. Can you post expected output based upon the sample data you provider earlier?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • March 22, 2010 at 10:28 am

    #1137677

    Hi Wayne

    When execute the above string , i must be able to get only the values from 1 - 100 (ie) only result set "12 test", since this has for a numeric that lies between the range 1-100 , Hope this explains 🙂

    cheers

  • March 22, 2010 at 1:14 pm

    #1137805

    CrazyMan (3/22/2010)

    Hi Wayne

    When execute the above string , i must be able to get only the values from 1 - 100 (ie) only result set "12 test", since this has for a numeric that lies between the range 1-100 , Hope this explains 🙂

    cheers

    So, in your initial post where you state

    Create table #temp ( text varchar(max))

    Insert into #temp values ('Test 123')

    Insert into #temp values ('12 test')

    Insert into #temp values ('1235')

    Insert into #temp values ('Test')

    ...

    is there any way i can use a expression which calculates if there are any numerics on the given string, as on above example, i must get 1 for first 3 values on temp table and 0 for the last one

    You actually want the first two to return a 1, and the last two to return a zero?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

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

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