Sybase UDF difficulty

Question

When I try to run the following function on Sybase ASE 15.7, it just spins indefinitely. Each component of the function seems to act as expected independently. This is simply a mechanism to strip all non-numeric characters from a string. Any and all thoughts appreciated.

create function dbo.mdsudf_just_digits(@str varchar(64))  
returns varchar(64)  
as  
begin  
    while patindex('%[^0-9]%', @str) > 0  
        set @str = stuff(@str, patindex('%[^0-9]%', @str), 1, '')  
    return @str  
end  

-- A typical invocation would be like so:

select dbo.mdsudf_just_digits('hello123there456')
```

score 1 · Accepted Answer · answered Jun 25 '21 at 13:37

In Sybase (ASE) the empty string ('') actually translates into a single space:

select '.'+''+'.'     -- period + empty string + period
go

 ---
 . .                  -- we get a space between the periods

So in the current stuff(@str, ..., 1, '') you're actually replacing the first non-numeric with a single space. This 'new' space then matches the non-number test on the next pass through the loop at which point the space is replaced with ... another space. This is leading to an infinite loop of constantly replacing the first non-number character with a space.

You can get around this by using NULL as the last arg to the stuff() call, eg:

set @str = stuff(@str, patindex('%[^0-9]%', @str), 1, NULL)

Sybase UDF difficulty

1 Answers1