SQL TRANSLATE()

The SQL TRANSLATE() function replaces specific characters in a string with corresponding characters from a given set.

This function is useful for replacing multiple characters in a string in one operation, such as translating symbols, converting letter cases, or cleaning up data.

The TRANSLATE() function is supported in Oracle and PostgreSQL but is not available in SQL Server or MySQL by default.

In this tutorial, we will go through SQL TRANSLATE() String function, its syntax, and how to use this function in SQL statements for string operations, with the help of well detailed examples.

Syntax of SQL TRANSLATE() Function

The basic syntax of the SQL TRANSLATE() function is:

</>
Copy
TRANSLATE(string, from_set, to_set);

Each part of this syntax has a specific purpose:

  • string: The text you want to modify by replacing certain characters.
  • from_set: A string containing the characters to be replaced.
  • to_set: A string containing the characters that will replace the characters in from_set. Each character in from_set is replaced by the corresponding character in to_set.

The TRANSLATE() function replaces each character in from_set found in the input string with the corresponding character from to_set.

Setup for Examples: Creating the Database and Table

We’ll create a sample documents table with fields doc_id and content. Follow these steps to set up the data for the TRANSLATE() function examples.

1. First, create a new database called library:

</>
Copy
CREATE DATABASE library;

2. Select the library database to work with:

</>
Copy
USE library;

3. Create a table named documents with the fields doc_id and content:

</>
Copy
CREATE TABLE documents (
    doc_id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT
);

4. Insert sample data into the documents table to use with the TRANSLATE() function examples:

</>
Copy
INSERT INTO documents (content)
VALUES
('The quick brown fox jumps over the lazy dog.'),
('This is a test document with #special&characters%'),
('Sample Content: ABC123'),
('Mask sensitive data like 456789 and display it safely.');

With this setup complete, you can run the TRANSLATE() function examples to test and view results in the documents table.

Examples: Using TRANSLATE() in SQL Queries

We’ll go through examples demonstrating the TRANSLATE() function in SQL, using sample data from a documents table with fields doc_id and content.

1. Replacing Multiple Characters in a String

To replace vowels “a”, “e”, and “i” in the content column with corresponding numbers “1”, “2”, and “3”:

</>
Copy
SELECT doc_id, 
       TRANSLATE(content, 'aei', '123') AS translated_content
FROM documents;

This query replaces all occurrences of “a” with “1”, “e” with “2”, and “i” with “3” in each content string.

2. Removing Special Characters from Text

To remove specific special characters (e.g., “&”, “#”, “%”) from the content column by translating them to nothing:

</>
Copy
SELECT doc_id, 
       TRANSLATE(content, '&#%', '') AS cleaned_content
FROM documents;

This query removes “&”, “#”, and “%” from each content entry by replacing them with an empty string.

3. Standardizing Case by Translating Characters

To replace uppercase “A”, “B”, and “C” with lowercase “a”, “b”, and “c” in the content column:

</>
Copy
SELECT doc_id, 
       TRANSLATE(content, 'ABC', 'abc') AS standardized_content
FROM documents;

This query converts uppercase “A”, “B”, and “C” to lowercase “a”, “b”, and “c” in each content entry.

4. Using TRANSLATE() for Masking Data

To mask digits in the content field by translating numbers “0” through “9” to asterisks “*”:

</>
Copy
SELECT doc_id, 
       TRANSLATE(content, '0123456789', '**********') AS masked_content
FROM documents;

This query replaces all numeric characters in each content entry with “*”, effectively masking any numerical information.

FAQs for SQL TRANSLATE()

1. What does the SQL TRANSLATE() function do?

The TRANSLATE() function replaces characters in a string based on a mapping from from_set to to_set for character replacement.

2. Can TRANSLATE() handle NULL values?

If the input string is NULL, TRANSLATE() returns NULL.

3. Is TRANSLATE() supported in all SQL databases?

No, TRANSLATE() is primarily supported in Oracle and PostgreSQL. SQL Server and MySQL do not support TRANSLATE() natively.

4. What happens if from_set and to_set lengths differ?

If from_set and to_set have different lengths, TRANSLATE() will replace characters only up to the length of to_set.

5. Can TRANSLATE() be used to remove characters from a string?

Yes, to remove characters, you can set to_set as an empty string, and all instances of characters in from_set will be deleted.