How to use the TRANSLATE() method in SQL
Maria ElijahThe 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 thestringparameter.replace_string: This represents the characters of a string to replace thestringif the position is found in thereplace_stringargument.
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 columnsid,name,level,gender, andstate. - Lines 11–21: We input data into the
Studenttable. - Lines 24–26: We use the
TRANSLATE()function to translate thenamecolumn. Finally, we display thenameand new columntlt_name. We order the result byid.
Note: Only the characters found in the
match_stringthat have a corresponding position in thereplace_stringare translated.
RELATED TAGS
CONTRIBUTOR
Maria ElijahView all Courses
