第六章 使用字符串
--6.7 提取姓名的大写首字母缩写
SELECT replace( translate( REPLACE('Dylan Xu', ' ','.'), 'abcdefghijklmnoopqrstuvwxyz', rpad('#',26,'#')), '#') || '.' FROM dual;
--6.8 按照字符串中部分内容排序
SELECT ename FROM emp ORDER BY substr(ename, -2, 2);
--6.9 按字符串中数字排序
CREATE OR REPLACE VIEW v_sqlbook1 AS SELECT e.ename ||' '|| CAST(e.empno AS CHAR(4))||' '|| d.dname AS DATA FROM emp e, dept d WHERE e.deptno = d.deptno;
SELECT data FROM v_sqlbook1 ORDER BY to_number(REPLACE( translate(DATA, REPLACE( translate(DATA, '0123456789', '##########'), '#'), rpad('#', 20, '#')), '#')) ;
SELECT to_number(trim(REPLACE( translate('abc 123 bnh', 'abcdefghijklmnopqrstuvwxyz', '##########'), '#'))) FROM dual;
--6.10 根据表中一行创建分隔列表
SELECT deptno, ltrim(sys_connect_by_path(ename, ','), ',') emps FROM ( select deptno, ename, row_number() over (partition by deptno order by empno) rn, count(*) over(partition by deptno) cnt from emp ) WHERE LEVEL = cnt START WITH rn = 1 CONNECT BY PRIOR deptno = deptno AND PRIOR rn = rn -1;
--6.11 将分隔数据转换为多值IN列表
--6.15 分析IP地址
SELECT instr('a,bc,def,g', ',', 1,1) FROM dual; SELECT ip, substr(ip, 1, instr(ip, '.') - 1) a, substr(ip, instr(ip, '.') +1, instr(ip, '.', 1, 2) - instr(ip, '.')-1 ) b, substr(ip, instr(ip, '.', 1, 2) +1, instr(ip, '.', 1, 3) - instr(ip, '.', 1, 2)-1 ) c, substr(ip, instr(ip, '.',1,3) + 1) d FROM (SELECT '192.168.1.240' AS ip FROM dual);