在工作中遇到一个问题,当时不会解决,了解translate函数后(语法translate('char','from_string','to_string'),char原始列,将from_string替换为to_string,且为一个字符一个字符的替换,且to_string不为空),进行试验验证最终明白了,以下是做的验证
SELECT ename || ' ' || empno AS data FROM EMP;-----查看原始数据效果
SELECT * FROM V;
SELECT DATA,translate(data,', 1234567890',',') AS ename FROM V order by 2;-----查看被替换筛选后的效果
其中红色的,逗号不做替换,只是占个位置,保证第三参数不为空,反之会出现空值,逗号可以用其他符号代替
别人也举了一个例子,我进行了试验,效果是一样的
CREATE OR REPLACE VIEW w AS
SELECT '-0' AS str FROM dual UNION ALL
SELECT '-123' AS str FROM dual UNION ALL
SELECT '-456' FROM dual UNION ALL
SELECT '-789' FROM dual UNION ALL
SELECT '-1.23' AS str FROM dual UNION ALL
SELECT '-.789' FROM dual UNION ALL
SELECT '-' FROM dual UNION ALL
SELECT '-123x' AS str FROM dual UNION ALL
SELECT '7-' FROM dual;
select * from w;
SELECT w.str, translate(str, '-0123456789', '-') AS str2 FROM w WHERE translate(str, '-0123456789', '-') = '-' AND str LIKE '-_%';
replace函数也可以进行替换,但是有什么不同呢,同样进行了测试
(图一)
(图二)
(图三)
SELECT DATA,replace(data,'78','#') AS ename from V order by 2;---结果为(图二)
SELECT DATA,translate(data,'78','#') AS ename from V order by 2;---结果为(图三)
replace函数是'78'作为一个整体去替换#,translate函数是将7和8分别替换为#,比较浅显的区别就是如此