SQL Server function execution - while statement behaviour unexpected

Question

I'm using this code to remove non numeric characters from a string:

ALTER FUNCTION [dbo].[StripNonNumerics](@Temp VARCHAR(255))
RETURNS VARCHAR
AS
BEGIN
    DECLARE @KeepValues AS VARCHAR(50);
    SET @KeepValues = '%[^0-9]%';

    WHILE PATINDEX(@KeepValues, @Temp) > 0 
    BEGIN
        SET @Temp = STUFF(@Temp, PATINDEX(@KeepValues, @Temp), 1, ''); 
    END

    RETURN @Temp
 END;

I expect that if I call this function, putting as input a string like this one:

select [dbo].[StripNonNumerics]('vAn34nd2')

I could get this output:

Instead, I obtain:

I changed input to test this behaviour, and I noticed that it takes only the first non numeric characters sequence. Could you tell me what I'm overlooking?

Thanks

Bad habits to kick : declaring VARCHAR without (length) - you should always provide a length for any varchar variables and parameters that you use. Otherwise, they might end up being a default length, which in case of a parameter or return value is 1 CHARACTER ..... — marc_s
– marc_s, Commented Aug 2, 2017 at 11:12

slaakso · Accepted Answer · 2017-08-02 10:26:56Z

4

Just change the return definition to read:

RETURNS varchar(255)

If you omit the length, the the default length is 1

answered Aug 2, 2017 at 10:26

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shakeer Mirza · Accepted Answer · 2017-08-02 10:29:42Z

2

I am just adding one another way of doing the above..

DECLARE @X VARCHAR(100)='vAn34nd2', @Y VARCHAR(100)=''

SELECT @Y = @Y+ VAL FROM(
SELECT SUBSTRING(@X,number,1) AS VAL 
FROM master.dbo.spt_values 
WHERE type='P' AND number BETWEEN 1 AND LEN(@X)
AND ISNUMERIC(SUBSTRING(@X,number,1))=1
)A

SELECT @Y

answered Aug 2, 2017 at 10:29

Shakeer Mirza

5,1182 gold badges20 silver badges42 bronze badges

Collectives™ on Stack Overflow

SQL Server function execution - while statement behaviour unexpected

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related