CODEWITHSUNDEEP
mysql
Pankaj
Pankaj

Reputation: 2754

Remove Spaces between between characters and numbers in a string

I am trying to remove spaces between characters and numbers in a string For example if I have "Value 01", I want "Value01" back . So I wrote a function, but it is giving me error Unexpected ), Also I am not sure if this is the most efficient way to do it. I tried searching for Regex Replace but couldn't find anything as such

CREATE DEFINER=`root`@`localhost` FUNCTION `RemoveSpaces`(
   myWord VARCHAR(1000)
) RETURNS VARCHAR(1000)

BEGIN
DECLARE RETURNSTRING VARCHAR(1000);

SET RETURNSTRING = (
SELECT 
   LEAST (
   if (Locate('0',myWord) >0, 
      REPLACE(myWord, ' 0', '0'),
   if (Locate('1',myWord) >0,
      REPLACE(myWord, ' 1', '1'),
   if (Locate('2',myWord) >0,
      REPLACE(myWord, ' 2', '2'),
   if (Locate('3',myWord) >0,
      REPLACE(myWord, ' 3', '3'),
   if (Locate('4',myWord) >0,
      REPLACE(myWord, ' 4', '4'),
   if (Locate('5',myWord) >0,
      REPLACE(myWord, ' 5', '5'),
   if (Locate('6',myWord) >0,
      REPLACE(myWord, ' 6', '6'),
   if (Locate('7',myWord) >0,
      REPLACE(myWord, ' 7', '7'),
   if (Locate('8',myWord) >0,
      REPLACE(myWord, ' 8', '8'),
   if (Locate('9',myWord) >0,
      REPLACE(myWord, ' 9', '9')
   )
 )
);
RETURN RETURNSTRING;
END

Upvotes: 1

Views: 7693

Answers (1)

jherran
jherran

Reputation: 3367

You don't need a function for that. Just use REPLACE in your query:

SELECT REPLACE("This is a string", " ", "") FROM dual;

And will return Thisisastring.

Upvotes: 5

Related Questions