translate — Performs char-by-char translation of given string


string translate ( strg string ,
search_list string ,
replace_list string );


The function returns the strg with occurrences of characters in the search_list replaced by the character at the corresponding position in the replace_list . If there is a character in the search_list with no character at a corresponding position in the replace_list (because the replace_list is longer than the replace_list ), then occurrences of that character in strg string are removed.

If a character occurs more than once in the search_list , then the first occurrence determines the replacement character. If the replace_list is longer than the search_list , then excess characters are ignored.

Two popular use cases for this function are case conversion and sorting with collation. For "to-upper" case conversion, the search_list consists of all lowercase characters of some language and the replace_list consists of all uppercase characters of that language. For "to-lower" case conversion, uppercase chars are in the search_list and lowercase are in the replace_list . For sorting with collation, the function must be used in "select" string expression attribute of <xsl:sort> element; the search_list consists of all characters reordered by collation and the replace_list consists of corresponding characters from "collation string".



String that must be translated.


String of characters that must be edited in the strg .


String of characters that must be inserted in the strg .

Return Types



Table24.153.Errors signalled by translate()

SQLState Error Code Error Text Description
XP001 XPF07 Too long string passed as argument 1 to XPATH function translate(), the result of translation is too long Virtuoso cannot process strings that are longer than 10M. This limit may be exceed if UTF-8 representations of the replacement characters are longer than representations of replaced characters; even if the number of encoded characters remains the same or decrements.


Example24.633.Trivial cases

The following expressions are true:

        translate("abracadabra","abc","ABC") = "ABrACAdABrA".
        translate("ab-ra-ca-dab-ra","abc-","ABC") = "ABrACAdABrA".

Example24.634.Sorting with collation in XSL

The following <xsl:sort> will sort records by its titles, making no difference between spaces and punctuation marks. In addition, spaces will be normalized after the collation processing.

        <xsl:key select="normalize-space(translate('@title','.,:;!?','      '))"/>