语法:REPLACE(被替换字符串,需替换的字符或变量,需显示的字符或变量)
例:显示所有员工的姓名,用a替换所有"A"
SCOTT@itkaifang> SELECT empno,ename FROM emp; EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 14 rows selected. SCOTT@itkaifang> SELECT REPLACE(ename,'A','a'),empno FROM emp; REPLACE(EN EMPNO ---------- ---------- SMITH 7369 aLLEN 7499 WaRD 7521 JONES 7566 MaRTIN 7654 BLaKE 7698 CLaRK 7782 SCOTT 7788 KING 7839 TURNER 7844 aDaMS 7876 JaMES 7900 FORD 7902 MILLER 7934 14 rows selected. SCOTT@itkaifang>
例:将所有员工ID号中的7显示为0。
SCOTT@itkaifang> SELECT REPLACE(empno,7,0),ename FROM emp; REPLACE(EMPNO,7,0) ENAME ---------------------------------------- ---------- 0369 SMITH 0499 ALLEN 0521 WARD 0566 JONES 0654 MARTIN 0698 BLAKE 0082 CLARK 0088 SCOTT 0839 KING 0844 TURNER 0806 ADAMS 0900 JAMES 0902 FORD 0934 MILLER 14 rows selected. SCOTT@itkaifang>