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
