SQL TRANSLATE

What is SQL TRANSLATE?

Replaces each character in a string that matches any character in a search set with the corresponding character in a replacement set.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL TRANSLATE: Supported: PostgreSQL, Oracle, SQL Server 2017+, IBM Db2, Amazon Redshift. Not supported: MySQL (string variant), SQLite.

SQL TRANSLATE Full Explanation

TRANSLATE is a character-by-character search-and-replace function. It scans an input string, looks for every occurrence of any character listed in the second argument, and substitutes it with the character that appears in the same position of the third argument. If the third argument is shorter than the second, characters with no counterpart are removed. TRANSLATE is executed in a single pass, making it faster than nesting multiple REPLACE calls. It is case-sensitive, works only on individual characters (not substrings), and returns NULL when any input is NULL.

SQL TRANSLATE Syntax

TRANSLATE ( source_string , search_chars , replace_chars );

SQL TRANSLATE Parameters

source_string (TEXT) - The string to be processed.
search_chars (TEXT) - Characters to find. Each character is treated independently.
replace_chars (TEXT) - Characters to substitute. Positionally matched to search_chars. If shorter, extra search characters are deleted.

Example Queries Using SQL TRANSLATE

-- 1. Mask digits
SELECT TRANSLATE('Phone: 415-555-1234', '0123456789', '##########');

-- 2. Remove vowels
SELECT TRANSLATE('DATABASE', 'AEIOUaeiou', '');

-- 3. Swap comma and period
SELECT TRANSLATE('3,141.59', ',.', '.,');

Expected Output Using SQL TRANSLATE

Returns 'Phone: ###-###-####'
Returns 'DTBS'
Returns '3.141,59'

Use Cases with SQL TRANSLATE

Quickly scrub or mask sensitive characters
Delete unwanted character classes (vowels, punctuation)
Convert locale-specific numeric formats
Replace multiple characters in one pass instead of chaining REPLACE

Common Mistakes with SQL TRANSLATE

Expecting substring replacement; TRANSLATE only works per character.
Supplying unequal search and replace strings and assuming missing replacements keep originals; they are removed.
Forgetting that NULL in any argument yields NULL.
Using on multi-byte characters; results depend on encoding and exact byte representation.

First Introduced In

SQL:1999 core specification; available in PostgreSQL 7.4 and Oracle 8i.

Frequently Asked Questions

How does TRANSLATE differ from REPLACE?

TRANSLATE works character-by-character, while REPLACE targets whole substrings. Use TRANSLATE when you need to swap or delete many single characters in one pass.

What happens when the replacement string is shorter than the search string?

Characters in the search string that lack a positional counterpart are removed from the result.

Is TRANSLATE case-sensitive?

Yes. 'A' and 'a' are treated as different characters unless you explicitly include both in the search list.

Can TRANSLATE handle Unicode characters?

It can, provided your database and column encoding support them. Each Unicode code point counts as one character for translation.