Oracle查询优化-使用字符串

--1.遍历字符串
--1.1.建立测试视图
CREATE OR REPLACE VIEW V AS
SELECT '天天向上' AS 汉字,'TTXS' AS 首拼 FROM DUAL;
--要求每个汉字对应相应的首拼字母显示
SELECT SUBSTR(汉字,LEVEL,1) 汉字,SUBSTR(首拼,LEVEL,1) 首拼,LEVEL
FROM V
CONNECT BY LEVEL <=4;
/*总结：CONNECT BY是树形查询的一个子句，后面的LEVEL是一个“伪列”，表示树形中的级别层次，通过LEVEL<=?循环？次，就形成？行数据，LEVEL以1为步长递增*/
--2.字符串文字中包含引号
SELECT 'I''M MR.YAN' PROFILE FROM DUAL;
SELECT NQ'[I'M MR.YAN']' PROFILE FROM DUAL;
SELECT NQ'{I'M MR.YAN'}' PROFILE FROM DUAL;
SELECT NQ'(I'M MR.YAN')' PROFILE FROM DUAL;
SELECT Q'<I'M MR.YAN>' PROFILE FROM DUAL;
/*总结：
 *1.在SQL中，'可以使用两个'来表达；
 *2.使用q-quoet特性：Q'{***}',***是所要输入的内容，如果其中包含单引号，那么单句执行可以正常显示，两个以上合并执行就会报错;
 *3.使用NQ'{***'}'可以消除界定符}前面的'，其实可以消除}前的任一单字符，这里是为了中和单数'所带来的的语法错误*/
--3.计算字符在字符串中出现的次数
--3.1.建立测试视图
CREATE OR REPLACE VIEW V AS
SELECT 'A,B,C' AS STRS FROM DUAL;
--3.2.ORACLE 11G新函数REGEXP_COUNT
SELECT REGEXP_COUNT(STRS,',')+1 AS CNT FROM V;
--3.3.使用REGEXP_REPLACE
SELECT LENGTH(REGEXP_REPLACE(STRS,'[^,]'))+1 AS CNT FROM V;
/*^代表否定的意思*/
--3.4使用TRANSLATE
SELECT LENGTH(TRANSLATE(STRS,','||STRS,','))+1 AS CNT FROM V;
--3.5.如果分隔符长度大于1，使用3.4的语句则有误
CREATE OR REPLACE VIEW V AS
SELECT 'A$%B$%C' AS STRS FROM DUAL;
--错误
SELECT LENGTH(TRANSLATE(STRS,'$%'||STRS,'$%'))+1 AS CNT FROM V;
--正确
SELECT LENGTH(TRANSLATE(STRS,'$%'||STRS,'$%'))/LENGTH('$%')+1 AS CNT FROM V;
--推荐
SELECT REGEXP_COUNT(STRS,'$%')+1 AS CNT FROM V;
--4.删除指定字符,如C和E
SELECT TRANSLATE('ABCDE','1'||'CE','1')AS STR FROM DUAL; 
SELECT REGEXP_REPLACE('ABCDE','[CE]')AS STR FROM DUAL; 
--5.区隔字母和数字
SELECT REGEXP_REPLACE('A1B2C3','[0-9]','') AS STR,REGEXP_REPLACE('A1B2C3','[^0-9]','') AS NUM FROM DUAL;
SELECT TRANSLATE('A1B2C3',' 0123456789',' ') AS STR,TRANSLATE('A1B2C3','0123456789'||'A1B2C3','0123456789') AS NUM FROM DUAL;
--6.查询只包含字母或数字型的数据
--6.1.建立测试视图
CREATE OR REPLACE VIEW V AS
SELECT '123' AS DATA FROM DUAL UNION ALL
SELECT 'ABC' FROM DUAL UNION ALL
SELECT 'ABC123' FROM DUAL UNION ALL
SELECT '123ABC' FROM DUAL UNION ALL
SELECT 'A1B2C3' FROM DUAL UNION ALL
SELECT '123ABC$#' FROM DUAL UNION ALL
SELECT '123$' FROM DUAL UNION ALL
SELECT 'A 5' FROM DUAL;
--6.2.使用正则表达式
SELECT DATA FROM V WHERE REGEXP_LIKE(DATA,'^[0-9A-Za-z]+$');
/*^不在方括号里时表示字符串开始，$表示字符串结束，+表示重复1次以上*/
--也可以使用*来代替+，*表示重复0次以上，也就是说比上一句多了一个判断条件：...OR LIKE ''...
SELECT DATA FROM V WHERE REGEXP_LIKE(DATA,'^[0-9A-Za-z]*$');
--与上一句效果一样，可见... OR LIKE ''并不影响结果，他实际不返回值
SELECT ENAME FROM EMP WHERE EMPNO LIKE '';
--7.提取姓名的大写首字母缩写
CREATE OR REPLACE VIEW V AS
SELECT 'Micheal Jackson' AS AL FROM DUAL;
SELECT REGEXP_REPLACE (V.AL,'([[:upper:]])(.*)([[:upper:]])(.*)','1.3') AS SX FROM V;
--8.根据表中的行创建一个分隔列
--8.1.WM_CONCAT（版本11.2以前返回值为VARCHAR,后面变为CLOB）
SELECT DEPTNO,SUM(SAL) AS TOTAL_SAL,WMSYS.WM_CONCAT(ENAME) AS TOTAL_ENAME FROM EMP GROUP BY DEPTNO;
--8.2.LISTAGG(src,'分隔符') WITHIN GROUP()
SELECT DEPTNO,SUM(SAL) AS TOTAL_SAL,LISTAGG(ENAME,',') WITHIN GROUP(ORDER BY ENAME) AS TOTAL_ENAME FROM EMP GROUP BY DEPTNO;
--9.提取第N个分隔的子串
--9.1.建立测试视图
CREATE OR REPLACE VIEW V AS
SELECT WM_CONCAT(ENAME) AS NAME FROM EMP WHERE DEPTNO IN ('10','20') GROUP BY DEPTNO;
SELECT * FROM V;
NAME
--------------------------------------------------------------------------------
CLARK,KING,MILLER
SMITH,ADAMS,FORD,SCOTT,JONES
--9.2.提取第二个名字，也就是KING和ADAMS
SELECT REGEXP_SUBSTR(V.NAME,'[^,]+',1,2) AS SUBSTR FROM V;
/**
 *1.参数2：“^"在方括号里表示否，+表示匹配1次以上，'[^,]+'表示匹配不包含逗号的多个字符，也就是各个子串
 *2.参数3：1表示从第一个字符开始
 *3.参数4:2表示第二个能匹配'[^,]+'的字符串，也就是KING和ADAMS
 */
--10.分解IP地址
--10.1.建立测试视图
CREATE OR REPLACE VIEW V AS
SELECT '192.168.0.66' AS IP FROM DUAL;
--10.2.分拆，因为IP中.的个数固定为3，所以不用加判断
SELECT REGEXP_SUBSTR(V.IP,'[^.]+',1,LEVEL) AS SUB_IP
FROM V
CONNECT BY LEVEL <=4;
--或直接转置为行
SELECT 
REGEXP_SUBSTR(V.IP,'[^.]+',1,1) AS A,
REGEXP_SUBSTR(V.IP,'[^.]+',1,2) AS B,
REGEXP_SUBSTR(V.IP,'[^.]+',1,3) AS C,
REGEXP_SUBSTR(V.IP,'[^.]+',1,4) AS D
FROM V;
--11.将分隔数据转换为多值IN列表
--11.1.建立测试视图
CREATE OR REPLACE VIEW V AS
SELECT 'YAN,WEI,TIAN' AS LOVE FROM DUAL;
--11.2.使用REGEXP_SUBSTR和LEVEL配合
SELECT REGEXP_SUBSTR(V.LOVE,'[^,]+',1,LEVEL) AS SUB_LOVE
FROM V
CONNECT BY LEVEL <=(SELECT LENGTH(TRANSLATE(LOVE,','||LOVE,','))+1 FROM V);
--12.按字母顺序排列字符串
--12.1.拆分
VAR V_ENAME VARCHAR2(50);
EXEC :V_ENAME := 'ADAMS';
SELECT :V_ENAME AS ENAME ,SUBSTR(:V_ENAME,LEVEL,1) AS C FROM DUAL
CONNECT BY LEVEL <= LENGTH(:V_ENAME);
--12.2.合并
SELECT ENAME,
  (
  SELECT LISTAGG(SUBSTR(ENAME,LEVEL,1),'') WITHIN GROUP (ORDER BY (SUBSTR(ENAME,LEVEL,1)) ASC) 
  FROM DUAL 
  CONNECT BY LEVEL<=LENGTH(ENAME)
  ) 
  AS NEW_NAME
FROM EMP;
--12.3.去重
SELECT ENAME,
       (
       SELECT LISTAGG(MIN(SUBSTR(ENAME,LEVEL,1)),'') WITHIN GROUP (ORDER BY MIN(SUBSTR(ENAME,LEVEL,1)) ASC) 
       FROM DUAL 
       CONNECT BY LEVEL<=LENGTH(ENAME) 
       GROUP BY SUBSTR(ENAME,LEVEL,1)
       ) 
       AS NEW_NAME
FROM EMP;