Oracle字符串函数应用举例

SELECT upper('abc') FROM dual;
SELECT lower(emp.ename) FROM emp;
TRIM(x),LTrim(x),Rtrim(x)


--取子串
SELECT substr('123456789',7) FROM dual;
SELECT substr('123456789',2,1) FROM dual;
--取位置
SELECT instr('123456789','56') FROM dual;
SELECT instr('1234123123','123',3) FROM dual;
SELECT instr('1234123123','123',3,2) FROM dual;
--判断一个字符串是否在另一个字符串
SELECT instr('123456789','a') FROM dual;




--填充字符串
--添加学生，学号=2位年份+班级号+4位流水号,业务主键
--0955 0001,0955 0022,0955 0456
SELECT Lpad(1,4,0) FROM dual;
SELECT to_char(SYSDATE,'yy') FROM dual;

SELECT to_char(SYSDATE,'yy')+'55'+Lpad(16,4,0) FROM dual;--错误
SELECT to_char(SYSDATE,'yy')||'55'||Lpad(16,4,0) FROM dual;

SELECT to_char(SYSDATE,'yy')||'55'||Lpad(seq1.nextval,4,0) FROM dual;
SELECT Lpad(99999,4,0) FROM dual;

SELECT Rpad(1,4,0) FROM dual;--工作中几乎不用


--b的理解
SELECT length('我好你好大家好') FROM dual;--字的个数
SELECT lengthB('我好你好大家好') FROM dual;--字的字节数