Replace a While Statement

Question

Post reply

Replace a While Statement

upperbognor

Right there with Babe

Points: 787
More actions
November 14, 2008 at 8:20 am

#215322

Hi, I was reviewing some code in our current system and came across a stored procedure with a bunch of While statements in it. I was wondering if there is a way to replace this recursion with SQL's new CTE recursion and get a better more efficient plan.
Demo code
create table #tmp_address ( generic_id decimal,
original_address nvarchar(150),
modified_address nvarchar(150),
box varchar(30),
)
Insert Into #Tmp_Address values(23168791,'Box Box 446','Box Box 446',Null)
Insert Into #Tmp_Address values(24617202,'Box Box 1957','Box Box 1957',NULL)
Insert Into #Tmp_Address values(24908633,'Box Box 1 SITE 1','Box Box 1 SITE 1',NULL)
Insert Into #Tmp_Address values(20074923,'Box 26080 STN POSTAL Box CTR', 'Box 26080 STN POSTAL Box CTR',NULL)
Select * from #Tmp_Address
while (select count(*)
from #tmp_address
where modified_address like '%Box [0-9]%') > 0
update #tmp_address
set box = ltrim( isnull(box, '') + ' ' + dbo.FN_getPattern('%Box [0-9]%', modified_address)),
modified_address = ltrim(rtrim(Replace(replace(modified_address, dbo.FN_getPattern('%Box [0-9]%', modified_address), ''),' ',' ')))
where modified_address like '%Box [0-9]%'
Select * from #Tmp_Address
drop table #tmp_address
This function is called by my demo code
Create function [dbo].[FN_getPattern] (@pattern varchar(100),
@string varchar(100)) returns varchar(30)
as
/*
select dbo.FN_getPattern ('%[0-9] Carl S%', 'Box 1 1234 Carl Street RR 45') --> '1234 Carl Street'
select dbo.FN_getPattern ('%BOX [0-9]%', 'Box 112 1234 Carl Street RR 45') --> 'Box 112'
select dbo.FN_getPattern ('%BOX [0-9][0-9]%', 'Box 112 ABD 1234 Carl Street RR 45') --> 'Box 112'
select dbo.FN_getPattern ('%BOX [0-9][0-9][0-9]%', 'Box 112 ABD 1234 Carl Street RR 45') --> 'Box 112'
select dbo.FN_getPattern('%Box [0-9A-Z][0-9A-Z] %','RR 3 Box 39 SITE 1')
select dbo.FN_getPattern('%Box [0-9A-Z][0-9A-Z-][0-9A-Z] %','RR 3 Box K-9 SITE 1')
*/
begin
-- 1. Find match.
declare @idx1 as integer
set @idx1 = patindex(@pattern, @string)
if @idx1 = 0 return null
-- 2. Find closest preceeding space
declare @chr as char(1)
declare @idx2 as integer
set @idx2 = @idx1
set @chr = substring(@string, @idx2, 1)
while @chr <> ' ' and @idx2 > 1 begin
set @idx2 = @idx2 - 1
set @chr = substring(@string, @idx2, 1)
end
-- 3. Find closest succeeding space
declare @idx3 as integer
set @idx3 = charindex(' ', @string, dbo.fn_patindexEnd(@pattern, @string))
if @idx3 = 0 set @idx3 = len(@string)
-- 4. return everything between the blanks.
return ltrim(substring(@string, @idx2, @idx3 - @idx2 + 1))
end --function
[/Code]
Code for function fn_PatIndexEnd
Create
function [dbo].[fn_patindexEnd](@pattern as varchar(100),
@string as varchar(100)) returns integer
as
/*
select dbo.fn_patindex('%kel%','kukelikusk', 1)
select dbo.fn_patindexEnd('%kel%','kukelikusk')
select dbo.fn_patindex('%Box [0-9A-Z][0-9A-Z] %','RR 3 Box 39 SITE 1', 1)
select dbo.fn_patindexEnd('%Box [0-9A-Z][0-9A-Z-][0-9A-Z] %','RR 3 Box K-9 SITE 1')
*/
begin
if patindex(@pattern, @string) = 0 return 0
declare @idx as integer
declare @revPat as varchar(100)
-- set @revPat = replace(replace(reverse(@pattern), ']9-0[', '[0-9]'), ']Z-A[', '[A-Z]')
set @revPat = replace(replace(replace(replace(replace(reverse(@pattern), '[', '('), ']', '['), '(', ']'), 'Z-A', 'A-Z'), '9-0', '0-9')
set @idx = len(@string) - dbo.fn_patindexLast(@revPat, reverse(@string))
return @idx + 1
end
code for function fn_PatIndexLast
[Code]
Create
function [dbo].[fn_patindexLast](@pattern as varchar(100),
@string as varchar(100)) returns integer
as
begin
declare @substr as varchar(100)
declare @idx as integer
set @idx = 0
while dbo.fn_patindex(@pattern, @string, @idx +1) > 0
set @idx = dbo.fn_patindex(@pattern, @string, @idx +1)
return @idx
end
[/code]
//edited to modify an alter statement to a create statement, and to add 2 functions PatIndexEnd, patindexLast

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

upperbognor,

Would you also post the code for the dbo.fn_patindexEnd function? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

upperbognor Right there with Babe Points: 787 More actions · Answer 2

Sorry Jeff, I did not notice that the function had an embedded function in it.

Thanks for the assistance.