XSolvo
Current user:   guest    Change     Preferences 
   List directory   History   Similar   Print version 
XSolvo 
   About XSolvo 
Articles 
html2xml 
tradufix 
TVProgramGrabber 
download 
Philosophy 
gallery 

Articles > Regular Expressions in SQL Server

 
rw-rw-r--   Stefan   wheel

Regular Expressions in SQL Server

  • First you need the extended stored procedure: xp_strutils.dll you can find it on the download page
  • Copy xp_strutils.dll to a directory thats in the path for the sql server (for example the System32 directory)
  • Open the query analyzer to execute the following commands.
-- when upgrading you must unload the dll from the memory
dbcc xp_strutils(free)

-- recreate the extended procedure
IF EXISTS(SELECT name,* FROM master..sysobjects
        WHERE name = 'xp_re_match' AND type = 'X')
    exec master..sp_dropextendedproc 'xp_re_match'
GO

exec master..sp_addextendedproc 'xp_re_match', 'xp_strutils.dll'
GO

-- recreate the extended procedure
IF EXISTS(SELECT name,* FROM master..sysobjects
        WHERE name = 'xp_re_match' AND type = 'X')
    exec master..sp_dropextendedproc 'xp_re_replace'
GO

exec master..sp_addextendedproc 'xp_re_replace', 'xp_strutils.dll'
GO

-- Test the procedures
exec master..xp_re_match @data='heJsan',@expression='(?-i)([aj])'

declare @txt varchar(8000)    
exec master..xp_re_replace 'heJsan','(?-i)([aj])','TEST',@txt out
print @txt

Now we create functions that wrapps the extended procedures

if exists (
select * from dbo.sysobjects where id = object_id(N'[dbo].[reReplace]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[reReplace]
GO


create function dbo.reReplace(
@text varchar(8000), @reExpression varchar(1024), @replacewith varchar(1024))
RETURNS varchar(8000)
AS
BEGIN  
  declare @txt varchar(8000)    
  exec master..xp_re_replace @text, @reExpression, @replacewith , @txt out
  RETURN(@txt)
END

if exists (
select * from dbo.sysobjects where id = object_id(N'[dbo].[reMatch]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[reMatch]
GO

create function dbo.reMatch(
@text varchar(8000), @reExpression varchar(1024))
RETURNS @restab  TABLE (matchno int, match varchar(1024))
AS
BEGIN  
--    declare @txt varchar(8000)    
    declare @matchcount int,
        @match1 varchar(1024),
        @match2 varchar(1024),
        @match3 varchar(1024),
        @match4 varchar(1024),
        @match5 varchar(1024),
        @match6 varchar(1024),
        @match7 varchar(1024),
        @match8 varchar(1024),
        @match9 varchar(1024),
        @match10 varchar(1024)

    set @matchcount = 0
    exec master..xp_re_match @text, @reExpression,
        @matchcount out,
        @match1 out,
        @match2 out,
        @match3 out,
        @match4 out,
        @match5 out,
        @match6 out,
        @match7 out,
        @match8 out,
        @match9 out,
        @match10 out

    while (@matchcount > 0)
    begin
        if @matchcount = 10
insert @restab(matchno, match) values(@matchcount,@match10)
        if @matchcount = 9
insert @restab(matchno, match) values(@matchcount,@match9)
        if @matchcount = 8
insert @restab(matchno, match) values(@matchcount,@match8)
        if @matchcount = 7
insert @restab(matchno, match) values(@matchcount,@match7)
        if @matchcount = 6
insert @restab(matchno, match) values(@matchcount,@match6)
        if @matchcount = 5
insert @restab(matchno, match) values(@matchcount,@match5)
        if @matchcount = 4
insert @restab(matchno, match) values(@matchcount,@match4)
        if @matchcount = 3
insert @restab(matchno, match) values(@matchcount,@match3)
        if @matchcount = 2
insert @restab(matchno, match) values(@matchcount,@match2)
        if @matchcount = 1
insert @restab(matchno, match) values(@matchcount,@match1)
        set @matchcount = @matchcount-1
    end

    return
END
views: 131


Reference Regular Expressions in SQL Server
http://www.xsolvo.com/Articles/RegularExpressionsInSQLServer


Prev. Number sequences   Transaction chunks Next