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

 
 
Thnx.
 Reference :

1) MSDN

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s