CODEWITHSUNDEEP
mysqlreplacechar
SpaceHeroGuide
SpaceHeroGuide

Reputation: 55

MySQL Replace with Char Function

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.

Upvotes: 3

Views: 2529

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562230

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.

Upvotes: 1

Related Questions