Sql cookbook学习札记_SQL

Sql cookbook学习笔记

以下是笔者在看cookbook时做的实验笔记，由于是PDF版，光看还是不行滴！（持续更新）

-------------------------------------------------------

第六章使用字符串

--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);


第七章使用数字