Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
postgresql

How to use the TRANSLATE() method in SQL

Maria Elijah

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.

RELATED TAGS

sql
postgresql
RELATED COURSES

View all Courses

Keep Exploring