April 10, 2008 at 2:40 am
Hello All
I am trying to create my first function in SQL Server 2005
I am basically trying to strip down the passed string (A Postcode District e.g. 'HX1') to remove all digits and return the result
I have the following for the function which runs OK:
ALTER FUNCTION [dbo].[PostArea](
@lcPostcode VARCHAR(4))
RETURNS VARCHAR(2)
AS
BEGIN
DECLARE @lcPostArea VARCHAR(2),
@lcString VARCHAR(50),
@lnPos INT,
@lcSearch VARCHAR(50)
SET @lcString = @lcPostcode
WHILE PATINDEX ( '%[^A-Za-z ]%' , @lcString)>0
SET @lnPos = PATINDEX ( '%[^A-Za-z ]%', @lcString )
SET @lcSearch = SUBSTRING ( @lcString, @lnPos, 1 )
SET @lcString = REPLACE ( @lcString, @lcSearch, '' )
SET @lcPostArea=@lcString
RETURN @lcPostArea
END
But when I try to execute the function it runs forever and never finishes
I am executing the function like this:
EXEC dbo.PostArea @lcPostcode = 'HX1'
I am expecting the return value of 'HX'
Any help would be great!
Thanks
Bicky1980
April 10, 2008 at 2:59 am
Your while loop needs a begin..end around the set statements...
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 10, 2008 at 2:59 am
Hi,
You have to place a BEGIN...END statement around the WHILE loop otherwise only the first statement after the WHILE statement will be processed, and you were effectively stuck in an infinite loop.
ALTER FUNCTION [dbo].[PostArea](
@lcPostcode VARCHAR(4))
RETURNS VARCHAR(2)
AS
BEGIN
DECLARE @lcPostArea VARCHAR(2),
@lcString VARCHAR(50),
@lnPos INT,
@lcSearch VARCHAR(50)
SET @lcString = @lcPostcode
WHILE PATINDEX ( '%[^A-Za-z ]%' , @lcString)>0
BEGIN
SET @lnPos = PATINDEX ( '%[^A-Za-z ]%', @lcString )
SET @lcSearch = SUBSTRING ( @lcString, @lnPos, 1 )
SET @lcString = REPLACE ( @lcString, @lcSearch, '' )
END
SET @lcPostArea=@lcString
RETURN @lcPostArea
END
Also, you cannot EXEC a function. You'd have to do
select dbo.PostArea('HX1')
April 10, 2008 at 3:03 am
Fantastic!
Works fine now....Thanks for the fast response and solution
😀
April 10, 2008 at 3:05 am
you cannot EXEC a function
That's not strictly true. This works...
DECLARE @lcPostArea VARCHAR(4)
EXEC @lcPostArea = dbo.PostArea @lcPostcode = 'HX1'
SELECT @lcPostArea
Although the "SELECT dbo.PostArea('HX1')" is of course shorter.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 11, 2008 at 2:10 pm
If I may put in my 2 cents.
ALTER FUNCTION [dbo].[PostArea](
@lcPostcode VARCHAR(4))
RETURNS VARCHAR(2)
Are you certain that a function that strips digits from a string will only ever be used in one place? Why not give it a generic name that expresses what it does and allow any size string? That way, if you later find this will be useful elsewhere, you don't have to modify it.
create function dbo.RemoveDigits(
@Pattern varchar(8000)
)
returns varchar(8000)
If, against all odds, you should never need it anywhere else, you've lost nothing. All gain, no cost. A pretty good trade.
WHILE PATINDEX ( '%[^A-Za-z ]%' , @lcString)>0 [begin]
SET @lnPos = PATINDEX ( '%[^A-Za-z ]%', @lcString )
There is never any reason to perform a calculation to evaluate the loop and perform the same calculation again within the loop. At the very worst, do the calculation just before getting to the loop, save the result, and then do the calculation in the loop at the bottom.
set @Var = some-calculation
while @Var < some-edge-value begin
-- code inside the loop
set @Var = some-calculation
end;--of loop
If you want this to work with digits (integer values 0 through 9) then write it using digits. The pattern '%[^A-Za-z ]%' is going to strip the string not only of all digits but punctuation marks and all other characters that is not a letter or a space. This speaks not only to making the function generally useful but also to avoiding unintended results. If you pass it 'A-B3' you may expect to get 'A-B' returned and may not immediately know why you are getting 'AB'. Surely some other developer calling your function based on nothing more than a description is going to be surprised.
With all that in mind, consider the following.
create function dbo.RemoveDigits(
@Pattern varchar(8000)
)
returns varchar(8000)
as begin
declare @Digit char(1);
set @Digit = '0';
while PatIndex( '%[0-9]%', @Pattern ) > 0 begin
set @Pattern = Replace( @Pattern, @Digit, '' );
set @Digit = @Digit + 1;
end;
return @Pattern;
end;-- Function
Possible improvements may be to change 'varchar(8000)' to 'nvarchar(4000)' or, if on version 2k5 or higher, '[n]varchar(Max)'.
This may seem like a lot of effort on my part for such a simple little function that was already fixed in subsequent replies. However, I saw a grasshopper in need and had to respond. Besides, I'm home sick as a dog today and I've got to do something to occupy my mind between fits of coughing.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply