translate函数说明

TRANSLATE(expr, from_string, to_string)

from_string 与 to_string 以字符为单位，对应字符一一替换。

SQL>  SELECT TRANSLATE('ab你好abcdefg', 'abcdefg', '1234567') AS NEW_STR FROM DUAL;

NEW_STR
-------------
12你好1234567

以字符为单位，对应字符一一替换。

替换规则:
a                     1
b                     2
你                    你     不作替换 参数里没有,不做替换
好                    好     不作替换
a                     1
b                     2
c                     3
d                     4
e                     5
f                     6
g                     7


如果to_string为空则返回空值,第3个参数为空
SQL> SELECT TRANSLATE('ab你好abcdefg', 'abcdefg', '') AS NEW_STR FROM DUAL;

N
-


SQL> 
会返回空值



问题一:替换掉字母
SELECT TRANSLATE('ab你好abcdefg', '1abcdefg', '1') AS NEW_STR FROM DUAL;

SQL> SELECT TRANSLATE('ab你好abcdefg', '1abcdefg', '1') AS NEW_STR FROM DUAL;

NEW_
----
你好


1  替换为   1
a            空
b            空
c            空
d            空
e            空
f            空
g            空



问题2 按数字字母混合字符串中的字母排序
SQL> CREATE OR REPLACE VIEW V
as
SELECT ename || ' ' || empno AS data FROM emp;  2    3  

视图已创建。

SQL> set linesize 200
SQL> select * from v where rownum<=4;


DATA
---------------------------------------------------
SMITH 7369
ALLEN 7499
WARD 7521
JONES 7566

SQL> SQL> 
SQL> SELECT data, translate(data, '-0123456789', '-') AS ename from v order by 2;

DATA						    ENAME
--------------------------------------------------- ------------------------------------------------------------------------------------------------------
ADAMS 7876					    ADAMS
ALLEN 7499					    ALLEN
BLAKE 7698					    BLAKE
CLARK 7782					    CLARK
FORD 7902					    FORD
JAMES 7900					    JAMES
JONES 7566					    JONES
KING 7839					    KING
MARTIN 7654					    MARTIN
MILLER 7934					    MILLER
SCOTT 7788					    SCOTT

DATA						    ENAME
--------------------------------------------------- ------------------------------------------------------------------------------------------------------
SMITH 7369					    SMITH
TURNER 7844					    TURNER
WARD 7521					    WARD

已选择14行。



CREATE OR REPLACE VIEW v 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;


SQL> select * from v;

STR
-----
-0
-123
-456
-789
-1.23
-.789
-
-123x
7-


SELECT v.str, translate(str, '-0123456789', '-') AS str2
  FROM v
 /*要求返回结果中没有“-”及数据以外的字符*/
 WHERE translate(str, '-0123456789', '-') = '-'
   /*第一位是“-”，第二位任意，保证了返回结果长度大于2*/
   AND str LIKE '-_%';