Regular Expression in Sqlserver 2005

Regular Expression in Sqlserver 2005.

why Regular Expression Required for sqlserver 2005? The basic need Of Regular Expression
is avoiding hard work for searching or pattern maching in database. Example if i
want to search valid email address from users table then what will be senario for
that? i have thousand of newly registered user daily. then how to i find invalid
Email Address from that? let’s start with regular expression in sqlserver 2005.
the basic requirement for that configure OLE Object in to Server. –Configure OLE
Automation object. –EXECUTE permission set for object ’sp_OACreate’

use[master] GO GRANT EXECUTE ON[sys].[sp_OASetProperty] TO [public] 
GO 
use[master] 
GO 
GRANT EXECUTE ON[sys].[sp_OAMethod] TO[public] 
GO use[master] 
GO GRANT EXECUTE ON[sys].[sp_OAGetErrorInfo] TO[public] 
GO use[master] 
GO GRANT EXECUTE ON[sys].[sp_OADestroy] TO[public] 
GO use[master] 
GO GRANT EXECUTE ON[sys].[sp_OAStop] TO[public] 
GO use[master] 
GO GRANT EXECUTE ON[sys].[sp_OACreate] TO[public] 
GO use[master] 
GO GRANT EXECUTE ON[sys].[sp_OAGetProperty] TO[public] 
GO sp_configure’show advanced options’,1 
GO reconfigure go  execsp_configure go execsp_configure‘Ole Automation Procedures’,
        

1 – Configuration option ‘Ole Automation Procedures’ changed from 0 to 1. Run the
RECONFIGURE statement to install. go reconfigure go steps : 1) Create Scalar Value
Function In Sqlserver 2005.

create FUNCTION dbo.RegEx     
(         
 --Expression Target / Source         
 @Target varchar(5000),         
 --Regular Expression Pattern         
 @Pattern varchar(Max),         
 --whether the expression case sensitive         
 @CaseSensitive bit = 0     
) 
RETURNS bit 
AS     
BEGIN         
DECLARE @ReturnOACreate int         
DECLARE @ObjToken int         
DECLARE @objMatches int         
DECLARE @objMatch int         
DECLARE @count int         
DECLARE @results bit          

EXEC @ReturnOACreate = sp_OACreate 'VBScript.RegExp', @ObjToken OUTPUT          
--objecttoken OUTPUT         
--Is the returned object token, and must be a local variable of data type int.         
--This object token identifies the created OLE object and is used in calls to the other         
--OLE Automation stored procedures.         
--Return         
--0 (success) or a nonzero number (failure) that is the integer value of the         
--HRESULT returned by the OLE Automation object          
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END          
EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'Pattern', @Pattern          
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END          
EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'Global', false         
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END          
EXEC @ReturnOACreate = sp_OASetProperty @ObjToken, 'IgnoreCase', @CaseSensitive         
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END              
EXEC @ReturnOACreate = sp_OAMethod @ObjToken, 'Test', @results OUTPUT, @Target         
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END          
EXEC @ReturnOACreate = sp_OADestroy @ObjToken         
IF @ReturnOACreate <> 0 
BEGIN             
    SET @results = 0             
    RETURN @results         
END     

--return 1 for success     
RETURN @results     
END          

2) Now use this function for Matching the Expression.

• 
  case 1: simple regular expression match
  

          select dbo.RegEx( N'122-45-6789', N'^\d{3}-\d{2}-\d{4}$',0 )      
          run this it return 0 it indicate this expression is valid.  
      
          select dbo.RegEx( N'1224-45-6789', N'^\d{3}-\d{2}-\d{4}$',0 )      
          run this it return 1 it indicate this expression is InValid.  
          

• 
  case 2: Get the data from Table now the senario is i am finding the valid email address from the table (here i used simple regular expression which is made manual for demo purpose you can use any of them.)
  

      declare @tblTmp as table(id int identity(1,1),emailaddress varchar(50))  
      insert into @tblTmp values ('test@yahoo') 
      insert into  @tblTmp values ('test@gmail.test') 
      insert into @tblTmp values ('t@gmail.com') 
      insert into @tblTmp values ('test@test.com') 
      insert into  @tblTmp values ('test@test.')  
      
      select * from @tblTmp  
      --find valid email address 
      
      select * from @tblTmp where dbo.RegEx(emailaddress,'^[A-Za-z0-9._%-]+@[a-zA-Z0-9]+\.[a-zA-Z]{2,4}$',0)=1