TRANSLATE()
functionThe 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.
TRANSLATE(string,matching_string, replace_string)
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.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 dataINSERT INTO StudentVALUES (02,'Paul Dons',100,'M','Lagos');INSERT INTO StudentVALUES (03,'Ameera Abedayo',300,'F','Imo');INSERT INTO StudentVALUES (04,'Maria Elijah',200,'F','Lagos');INSERT INTO StudentVALUES (05,'David Hassan',500,'M','Abuja');INSERT INTO StudentVALUES (06,'Niniola Disu',100,'F','Lagos');INSERT INTO StudentVALUES (08,'Joe Smith',100, 'M','Lagos');-- QuerySELECT name, TRANSLATE(name, 'Haesdays', '123456') AS tlt_nameFROM StudentORDER BY id;
In the code above, we see the following:
Student
, which has the columns id
, name
, level
, gender
, and state.
Student
table.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 thereplace_string
are translated.