The SQL TRANSLATE function is worth knowing about if you ever find yourself needing to (for example) fix data from an external feed before you attempt to do anything with it.
The function requires returns a value in which one or more characters in
expression have been converted into other characters. You can pass it up to four parameters: the expression (obviously); the to-string; the from-string and a pad character.
You can use the function with only the expression, in which case the characters are simply converted to uppercase, which is a bit pointless. Things become useful when you specify the
If more than one argument is specified, the result string is built character by character from
expression, converting characters in
from-stringto the corresponding character in
to-string. For each character in
expression, the same character is searched for in
from-string. If the character is found to be the nth character in
from-string, the resulting string will contain the nth character from
to-stringis less than n characters long, the resulting string will contain the pad character. If the character is not found in
from-string, it is moved to the result string unconverted.
Here’s an example.
I’ve knocked together a sample table with two columns — a code and a description — to represent a data feed. The description can contain accented characters, but we don’t want to see these in the target system.
The unadjusted table looks like this:
IDX CODE DESCRIPTION 1 FAC01 Façile 2 UML01 Umlaüt
To get rid of those accents in the description, this query…
select IDX, CODE, translate(DESCRIPTION, 'eeaaocuouee', 'éèâàôçûöüëê') from FEED
… returns this result:
IDX CODE TRANSLATE 1 FAC01 Facile 2 UML01 Umlaut
from-string is longer than the
to-string, the pad character is used to pad the
to-string to ensure the lengths match. So, in the above example,
select translate(CODE, 'xx', '01') from FEED
… is equivalent to this one…
select translate(CODE, 'x', '01', 'x') from FEED
I’m not entirely convinced of the value of this argument. It is probably bot safer and clearer to simply ensure your
from-string are the same length.