1、遍歷字串
2、計算字元在字串中出現的次數
3、從字串中刪除不需要的字元
4、將字元和數字資料分離
5、判別字串是不是字母數字型的
6、提取姓名的大寫首字母縮寫
7、按字串中的部分內容排序
8、按字串中的數值排序
9、根據表中的行建立一個分隔列表
10、按字母順序排列字串
11、判別可作為數值的字串
12、提取第n個分隔的子串
13、分解IP地址
1、遍歷字串
1)、select * from t10 order by id asc;
ID
———-
1
2
3
4
5
6
7
8
9
10
2)、
select substr(e.ename, iter.id, 1) as c
from (select 'abcdefg' ename from dual) e,
(select * from t10 order by id) iter
where iter.id <= length(e.ename)
說明，t10表只是個輔助表而已，書上稱之為“基幹表”。在這裡有一點需要注意的就是這裡的字串'abcdefg'長度是小於10的，所以在這裡使用t10可以實現我們的需求。假若字串長度大於10，那麼就不行了。所以我們應該保證基幹表中的行數要大於字串的長度才行。
2、計算字元在字串中出現的次數
例如：'10,cleak,manager'計算在這字串中有多少個逗號。
思路：首先計算出原字串的長度，然後計算去掉逗號後字串的長度，這兩者的差就是逗號在該字串中出現的次數。
區別：length求得是字元長度，lengthb求得是位元組長度。
方法一
select lengthb('10,cleak,manager')-lengthb(replace('10,cleak,manager', ',', '')) count from dual;
方法二
select lengthb(translate('10,cleak,manager', ','||'10,cleak,manager', ',')) count from dual;
方法三，適用於11g以上
select regexp_count('10,cleak,manager', ',') count from dual;
3、從字串中刪除不需要的字元
例如，現在要求從ENAME列中刪除母音字母(a,e,i,o,u)，從SAL列中刪除0。
說明，REPLACE、TRANSLATE函式用法
select ename,
–先使用translate()把母音字母都轉換為一個符號','，完了再使用replace去掉符號','
replace(translate(ename, upper('aeiou'), ','), ',', '') as sub_ename,
sal,
replace(sal, 0, '') as sub_sal
from emp;
4、將字元和數字資料分離
例如，select ename||sal as data from emp，現在要求把data分開為兩列，字元部分一列，數字部分一列。
說明，LPAD(String a, int length, String addString)，作用：把addString新增到a的左邊，length是返回值的長度。
select replace(translate(data, '1234567890', '0000000000'), '0', '') as ename,
to_number(replace(translate(lower(data),
lower('abcdefghijklmnopqrstuvwxyz'),
rpad('z', 26, 'z')),
'z',
'')) as sal
from (select ename || sal as data from emp);
5、判別字串是不是字母數字型的
create view V as
select ename as data
from emp
where deptno=10
union all
select ename||', $'||cast(sal as varchar2(10))||'.00' as data
from emp
where deptno=20
union all
select ename||cast(sal as varchar2(20)) as data
from emp
where deptno=30
現在要求查詢出為字母數字型的行，也就是說4-8要去掉，因為其中除了字母和數字還包含了其他字元。
select data
from v
where translate(lower(data),
'0123456789abcdefghijklmnopqrstuvwxyz',
rpad('z', 36, 'z')) = rpad('z', length(data), 'z');
6、提取姓名的大寫首字母縮寫
例如，Stewie Griffin，要返回結果如：S.G.
select replace(replace(translate(replace('Stewie Griffin','.',''),
'abcdefghijklmnopqrstuvwxyz',
rpad('#',26,'#')),'#',''),
' ',
'.')||'.'
from dual
7、按字串中的部分內容排序
select ename, substr(ename, length(ename)-1, 2) subename from emp order by substr(ename, length(ename)-1, 2)
8、按字串中的數值排序
select data,
to_number(replace(translate(data,replace(translate(data,'0123456789','##########'),'#'),rpad('#',20,'#')),'#')) num
from V order by
to_number(replace(translate(data,replace(translate(data,'0123456789','##########'),'#'),rpad('#',20,'#')),'#'))
9、根據表中的行建立一個分隔列表
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
10、按字母順序排列字串
————————-
ADAMS AADMS
ALLEN AELLN
BLAKE ABEKL
CLARK ACKLR
FORD DFOR
JAMES AEJMS
JONES EJNOS
KING GIKN
MARTIN AIMNRT
MILLER EILLMR
SCOTT COSTT
OLD_NAME NEW_NAME
sql語句
SELECT OLD_NAME, NEW_NAME
FROM (SELECT OLD_NAME, REPLACE(SYS_CONNECT_BY_PATH(C, ' '), ' ') NEW_NAME
FROM (SELECT E.ENAME OLD_NAME,
ROW_NUMBER() OVER(PARTITION BY E.ENAME ORDER BY SUBSTR(E.ENAME, ITER.POS, 1)) RN,
SUBSTR(E.ENAME, ITER.POS, 1) C
FROM EMP E, (SELECT ROWNUM POS FROM EMP) ITER
WHERE ITER.POS <= LENGTH(E.ENAME)
ORDER BY 1)
START WITH RN = 1
CONNECT BY PRIOR RN = RN – 1
AND PRIOR OLD_NAME = OLD_NAME)
WHERE LENGTH(OLD_NAME) = LENGTH(NEW_NAME);
11、判別可作為數值的字串
12、提取第n個分隔的子串
13、分解IP地址
SELECT IP,
SUBSTR(IP, 1, INSTR(IP, '.', 1, 1) – 1) A,
SUBSTR(IP,
INSTR(IP, '.', 1, 1) + 1,
INSTR(IP, '.', 1, 2) – INSTR(IP, '.', 1, 1) – 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.112.101' AS IP FROM DUAL);