Using the SQL Translate Function

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 to-string and from-string:

If more than one argument is specified, the result string is built character by character from expression, converting characters in from-string to 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-string. If to-string is 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    

If the 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,

This query:

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 to-string and from-string are the same length.