none
regular expressions in transact sql

    Question

  • hi guys,

    i need some help tp write code in order to search the string ( regular expressions) in t- sql.

    e.g.

    when a user enters [A-Z] it means any alphabet from 'A' to 'Z'...

    similarly [0-9] means any digit.

    the problem is: when user enters [0-6] and the string received contains digit 5 it should return true but if it contains 7 it should return false.

    so how do i read the [A-Z] as a range of characters in t-sql?

     

    Monday, December 11, 2006 5:40 AM

Answers

  • hi,

    In sql server it is not exactly regular expression, it is called wild card pattern. in other words it is simplified reqular expression,

    as of now SQL Server Like operator only work with following operators

    % - Zero or any number of chars

    _ - Single Char

    [] - Single Char in given range

    Cake - Single Char not in given range

    if you want to utilize the exact regular expression on your query then the best solution will be CLR Functions.(SQL Server 2005).

    For fixed validation (only numbers & only alphabets) i achived the following function,


    Create Function dbo.IsMatching(@Value as varchar(1000), @Pattern as varchar(100))
    returns bit as
    Begin 
     Declare @Len as int;
     Declare @SearchPattern as varchar(8000);
     Declare @Result as Int;

     Select @Len = Len(@Value);

     While @Len>0
     Begin
      Select @SearchPattern = Isnull(@SearchPattern,'') + @Pattern;
      Select @Len = @Len -1;
     End
     Select @Result = Case When @Value Like @SearchPattern Then 1 Else 0 End;
     Return @Result;
    End

    Go

    select dbo.IsMatching('SQLServer','[A-Z]') as Result

    Result : 1

    select dbo.IsMatching('SQL Server','[A-Z]') as Result

    Result : 0 --Space on String

    select dbo.IsMatching('SQL Server','[A-Z ]') as Result

    Result : 1 --Space added on Pattern

    select dbo.IsMatching('12453','[1-5]') as Result

    Result : 1

    select dbo.IsMatching('12463','[1-5]') as Result

    Result : 0

    Monday, December 11, 2006 6:18 AM

All replies

  • hi,

    In sql server it is not exactly regular expression, it is called wild card pattern. in other words it is simplified reqular expression,

    as of now SQL Server Like operator only work with following operators

    % - Zero or any number of chars

    _ - Single Char

    [] - Single Char in given range

    Cake - Single Char not in given range

    if you want to utilize the exact regular expression on your query then the best solution will be CLR Functions.(SQL Server 2005).

    For fixed validation (only numbers & only alphabets) i achived the following function,


    Create Function dbo.IsMatching(@Value as varchar(1000), @Pattern as varchar(100))
    returns bit as
    Begin 
     Declare @Len as int;
     Declare @SearchPattern as varchar(8000);
     Declare @Result as Int;

     Select @Len = Len(@Value);

     While @Len>0
     Begin
      Select @SearchPattern = Isnull(@SearchPattern,'') + @Pattern;
      Select @Len = @Len -1;
     End
     Select @Result = Case When @Value Like @SearchPattern Then 1 Else 0 End;
     Return @Result;
    End

    Go

    select dbo.IsMatching('SQLServer','[A-Z]') as Result

    Result : 1

    select dbo.IsMatching('SQL Server','[A-Z]') as Result

    Result : 0 --Space on String

    select dbo.IsMatching('SQL Server','[A-Z ]') as Result

    Result : 1 --Space added on Pattern

    select dbo.IsMatching('12453','[1-5]') as Result

    Result : 1

    select dbo.IsMatching('12463','[1-5]') as Result

    Result : 0

    Monday, December 11, 2006 6:18 AM
  • thanks mani,

     got the [A-Z] and [^a-z] concept.

    my other requirements are to match zero or more characters and to match one or more characters.

    e.g. T*he should match he, the, tthe, ttttthe.. etc.

    and t+ho should match tho, thho, thhhhhhho, thhhhhhhhhhhhhho.. etc.

    the above operators i have used in VC++,

    do they work in t-sql too?

     

    Monday, December 11, 2006 9:19 AM
  • If you need regular expression and your platform is sqlserver 2005 you can use a CLR strored procedure. If you need help on this post a question on the .net framework inside sql server forum
    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=86&SiteID=1
    Monday, December 11, 2006 9:21 AM
  • Yes.. if you want to find the repeated chars you can use the following condtions....

      columnname not like '%aaa%'
      and columnname not like '%bbb%'
      and columnname not like '%ccc%'
      and columnname not like '%ddd%'
      and columnname not like '%eee%'
      and columnname not like '%fff%'
      and columnname not like '%ggg%'
      and columnname not like '%hhh%'
      and columnname not like '%iiii%'
      and columnname not like '%jjj%'
      and columnname not like '%kkk%'
      and columnname not like '%lll%'
      and columnname not like '%mmm%'
      and columnname not like '%nnn%'
      and columnname not like '%ooo%'
      and columnname not like '%ppp%'
      and columnname not like '%qqq%'
      and columnname not like '%rrr%'
      and columnname not like '%sss%'
      and columnname not like '%ttt%'
      and columnname not like '%uuu%'
      and columnname not like '%vvv%'
      and columnname not like '%www%'
      and columnname not like '%xxx%'
      and columnname not like '%yyy%'
      and columnname not like '%zzz%'

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=996813&SiteID=1

    If you want to utilize the exact regular expression as i said earlier you can go for CLR Functions..

    Monday, December 11, 2006 9:55 AM
  • I want to make sure that the string entered by the user consists only of alphabets and/or digits.
    I saw the above function created by mani; but that only seems to take care of patterns which are either ONLY alphabets OR
    ONLY digits?
    is there a way to check for patterns which might look for either and not accept characters like (,),*,+,-,/,\,!,£,$^ etc??
    NOTE: the length of the string to be checked may vary

    Friday, August 28, 2009 3:51 PM