SQL function with replace statement returning with junk value

Question

Created simple function to remove few special character but it is returning with junk values:

ALTER FUNCTION [dbo].[fnRemoveInvalidChars] (@Temp VARCHAR(300))
RETURNS VARCHAR(300)
BEGIN

   SELECT @Temp = REPLACE(REPLACE(@Temp, ',', ''), '.', '') /* removing , and .*/
   return @Temp;
END

Returning values are like below:

58,871,300.00   => 5.88713e+007
55,146,000.00   => 5.5146e+007
8,296,000.00    => 8.296e+006

Important to note that, the value i am passing can be Varchar, decimal,int or float and because of that it is not working correctly.

Why you're using set @Temp = CONVERT(VARCHAR,@Temp);? Do you just want to remove '.' and ',' from a string? — Ilyes
– Ilyes, Commented Sep 10, 2019 at 7:28
@Temp is can be a Varchar, decimal or float... we can remove that if needed — Suresh Kamrushi
– Suresh Kamrushi, Commented Sep 10, 2019 at 7:29
Your function is fine. It must be how you are passing the value to the function. How is the function called ? — Squirrel
– Squirrel, Commented Sep 10, 2019 at 8:20

Ilyes · Accepted Answer · 2019-09-10 07:36:01Z

1

First, alter your function as (although your function is working just fine):

ALTER FUNCTION [dbo].[fnRemoveInvalidChars] (@Temp VARCHAR(300))
RETURNS VARCHAR(300)
BEGIN
  DECLARE @Buff VARCHAR(300);
  SELECT @Buff = REPLACE(REPLACE(@Temp, ',', ''), '.', '');
  RETURN @Buff;
END

Then you can use it as

SELECT [dbo].[fnRemoveInvalidChars]('58,871,300.00')
UNION
SELECT [dbo].[fnRemoveInvalidChars]('55,146,000.00')
UNION
SELECT [dbo].[fnRemoveInvalidChars]('8,296,000.00');

The results is:

5514600000
5887130000
829600000

Demo

answered Sep 10, 2019 at 7:36

Ilyes

15k5 gold badges31 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

SQL function with replace statement returning with junk value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related