Function error -- sql server programming

Marvin
2/27/2004 8:28:39 PM

Hiya,
I have an error in this function
It is supposed to remove any non-numeric characters.

a value of 3v345 would come back as 3345.

But why does it return a NULL if the first character is non-numeric?
i.e. v3345 comes back NULL rather than 3345

thanks!!

CREATE FUNCTION [dbo].[StripNonNumeric] (@strIn varchar(30))

RETURNS FLOAT AS
BEGIN
declare @intCounter int
declare @strTmp varchar(300)
declare @chrTmp varchar(1)

set @intCounter = 1
set @strTmp = ''
WHILE @intCounter <Len(@strIn)+1
BEGIN
set @chrTmp = substring(@strIn,@intCounter,1)

-- If ( IsNumeric(@chrTmp) =1) and @chrTmp<>'$'
if @chrTmp NOT LIKE '%[^0123456789]%'
begin
set @strTmp = @strTmp + @chrTmp

End

set @intCounter =@intCounter +1

if @strTmp = ''
begin
set @strTmp = null

end

END

return @strTmp

END

Steve Kass
2/27/2004 10:48:18 PM

Marvin,

The assignment of null here:

if @strTmp = ''
begin
set @strTmp = null

is happening right after the first character is checked. Moving that
statement down so that it is after the END of the while loop should fix
this problem.

SK

[quoted text, click to view]

Marvin
2/27/2004 11:13:47 PM

Thanks!

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Function error