How to use the TRANSLATE() method in SQL

The SQL TRANSLATE() function

The TRANSLATE() function is used to translate the characters of a given string by the characters in the specified replace_string.

The TRANSLATE() function takes each character in the given string (first argument) and searches through the match_string (second argument).

If the character is found at a certain position in the match_string, but there’s no substitute character defined for that position in the replace_string (third argument), then no translation will occur. Otherwise, the character will be translated.

Also, if the character is not found in the match_string, the translation will not take place.

Syntax

TRANSLATE(string,matching_string, replace_string)

Parameter

  • string: This represents the string to be translated.
  • match_string: This represents the string to be matched with each character in the string parameter.
  • replace_string: This represents the characters of a string to replace the string if the position is found in the replace_string argument.

Example

The following code demonstrates how to use the TRANSLATE() function in SQL.

CREATE TABLE Student (
    id int,
    name varchar(50),
    level int,
    gender varchar(10),
    state varchar(15)
);
-- Insert data
INSERT INTO Student
VALUES (02,'Paul Dons',100,'M','Lagos');
INSERT INTO Student
VALUES (03,'Ameera Abedayo',300,'F','Imo');
INSERT INTO Student
VALUES (04,'Maria Elijah',200,'F','Lagos');
INSERT INTO Student
VALUES (05,'David Hassan',500,'M','Abuja');
INSERT INTO Student
VALUES (06,'Niniola Disu',100,'F','Lagos');
INSERT INTO Student
VALUES (08,'Joe Smith',100, 'M','Lagos');
-- Query 
SELECT name, TRANSLATE(name, 'Haesdays', '123456') AS tlt_name
FROM Student
ORDER BY id;

Explanation

In the code above, we see the following:

  • Lines 1–7: We create a table called Student, which has the columns id, name, level, gender, and state.
  • Lines 11–21: We input data into the Student table.
  • Lines 24–26: We use the TRANSLATE() function to translate the name column. Finally, we display the name and new column tlt_name. We order the result by id.

Note: Only the characters found in the match_string that have a corresponding position in the replace_string are translated.

Free Resources

License: Creative Commons-Attribution-ShareAlike 4.0 (CC-BY-SA 4.0)