TRANSLATE function replaces a string with another set of characters.TRANSLATE replace characters in sequential order.
Syntax
TRANSLATE(string, from_string, to_string)
Example 1:-
SQL> select translate(‘sqlandplsql’, ‘sql’, ‘sequel’) as str from dual;
STR
———–
seqandpqseq
Oracle first find all occurrence of string ‘sql’ and replaces with ‘sequel’. The above string has two occurrences and replaced both of them.
Example 2:-
SQL> select translate(‘sqlandplsql’, ‘sql’, ‘SQL’) as str from dual;
STR
———–
SQLandpLSQL
Example 3:-
SQL> select translate(‘sqlandplsql’, ‘l’, ‘9’) as str from dual;
STR
———–
sq9andp9sq9
Example 4:-
SQL> select translate(‘sqlandplsql’, ‘sql’, ”) as str from dual;
S
—-
null
If no matches then TRANSLATE returns null.
Related posts:
Advertisements
Translate is very much used in Oracle. With string Group function. Nice example…..
select translate(‘sqlandplsql’, ‘l’, ‘9’) as str from dual;
STR
———–
sq9andp9sq9
but my reqirement only one 9 replace in l
i.e sq9andplsql i want this answer how to write quary
Try using replace function
why does
select TRANSLATE(‘aAbB(425)555-121”2 w”m %@$##& z’
,’Z (){}[]/\;:.,-_=+*&^%$#@!””’
,’1′) A
from dual
return: aAbB4255551212wmz
?