MySQL Replace with Char Function

Question

I try to create a MySQL function to parse a given string into a version I can use in an URL. So I have to eliminate some special characters and I try to do it with a loop to keep the code simple and to not have to specify any character.

My current code is:

DECLARE parsedString VARCHAR(255);

# convert to low chars
SET parsedString    =   CONVERT(paramString USING latin1);
SET parsedString    =   LOWER(parsedString);

# replace chars with A
SET @x = 223;

charReplaceA: WHILE @x <= 229 DO
    SET @x = @x + 1;

    SET parsedString    =   REPLACE (parsedString, CHAR(@x USING ASCII), 'a');
END WHILE charReplaceA;

# convert to utf8 and return
RETURN CONVERT(parsedString USING utf8);

If I try to use my code it doesn't work. Somehow it doesn't recognize the CHAR(@x USING ASCII) part.

SELECT urlParser('aäeucn');

returns

aäeucn

If I change my code to

SET parsedString    =   REPLACE (parsedString, 'ä', 'a');

it somehow works and returns

aaeucn

Does anyone have any idea how to use REPLACE() with CHAR()? I don't want to specify any possible character.

I realized the ASCII code numbers for MySQL are different than the PHP codes. Using PHP 228 returns an ä, using MySQL ASCII('ä') returns 195. But if I try to use REPLACE() with CHAR(195 USING ASCII) it doesn't work either. — SpaceHeroGuide
– SpaceHeroGuide, Commented Dec 26, 2012 at 17:27

Bill Karwin · Accepted Answer · 2012-12-26 18:02:31Z

1

You could try

REPLACE(CONVERT('aäeucn' USING ascii), '?', 'a')

When you convert international characters to ascii, all non-ascii characters are represented by a literal '?' character. Then you don't have to do the loop (so it will probably run a lot faster).

Also consider other methods of encoding international characters in URLs.
See http://www.w3.org/International/articles/idn-and-iri/

Re your comment:

If you need a character-by-character replacement, I wouldn't do it in an SQL function. MySQL functions are not efficient, and coding them and debugging them is awkward. I'd recommend fetching the utf8 strings back into your application and do the character translation there.

You didn't specify which application programming language you're using, but for what it's worth, PHP supports a function strtr() that can be used for exactly this scenario. There's even an example of mapping i18n characters to ascii characters in the manual page: http://php.net/strtr

$addr = strtr($addr, "äåö", "aao"); // That was easy!

That solution will be far faster and easier to code than a MySQL stored function.

edited Dec 26, 2012 at 18:02

answered Dec 26, 2012 at 17:27

Bill Karwin

567k87 gold badges711 silver badges872 bronze badges

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

2 Comments

SpaceHeroGuide Over a year ago

That would work, but it would replace any special character with an 'a'. But i'd like to replace ä, á, â, ... with an 'a'; é, ê, ... with an e and so on.

SpaceHeroGuide Over a year ago

Ok, then I just stick with my PHP solution. Thank you!

Collectives™ on Stack Overflow

MySQL Replace with Char Function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related