(1)lower,upper:转换大小写
SELECT ename,
lower(ename),
upper(ename)
FROM new_emp;
(2)initcap:单词首字母大写,其余小写
SELECT initcap('This IS a book') FROM dual;
(3)concat(字符串1,字符串2):拼接字符串
SELECT concat('hello','world')
FROM dual;
select 'hello' ||'world'||'xxxx'||'cccc' from dual;
(4)substr(字符串,起始位置[,截取个数]):截取一个字符串的子串,起始位置可以为负数(右数第N位)
SELECT substr('abcdefg',3) FROM dual; --cdefg
SELECT substr('abcdefg',3,2) FROM dual; --cd
SELECT substr('abcdefg',-3) FROM dual; --efg
SELECT substr('abcdefg',-3,2) FROM dual; --ef
(5)length:返回字符串长度
SELECT length('abcd') from dual; --4
SELECT length('') from dual; --null
(5)instr(字符串,查找子串[,起,[,第几次出现]]):查找字符串中子串的起始位置,如果找不到返回0,
起始位置也可以为负数(从右向左反向搜索)
SELECT instr('abcdefg','cd') FROM dual; --3
SELECT instr('abcdefg','cdf') FROM dual; --0
SELECT instr('abcdefgcdefg','cd') FROM dual; --3
SELECT instr('abcdefgcdefg','cd',4) FROM dual; --8
SELECT instr('abcdefgcdefg','cd',-1) FROM dual; --8
SELECT instr('abcdefgcdefgcde','cd',1,2) FROM dual; --8
(6)lpad(字符串,固定长度,填充字符):左填充
PS:rpad:同上,右填充
SELECT lpad('abcd',7,'#') FROM dual;--###abcd
SELECT lpad('abcd',3,'#') FROM dual;--abc
SELECT rpad('abcd',7,'#') FROM dual;--abcd###
SELECT lpad('',7,'*') FROM dual;--null
SELECT lpad(' ',7,'*') FROM dual;--******
SELECT lpad('abcd',7.9,'*') FROM dual;--***abcd
(7)trim(关键字 from 字符串):修剪字符串两边的关键字
SELECT trim('a' FROM 'aabcdaaxyza') FROM dual; --bcdaaxyz
PS:ltrim 修剪左边的空格,rtrim 修剪右边的空格
SELECT 123||ltrim(' abcd ')||456 FROM dual;--123abcd 456
SELECT 123||rtrim(' abcd ')||456 FROM dual;--123 abcd456
SELECT rtrim('aabcdaaxyza','a') FROM dual;--aabcdaaxyz
SELECT ltrim('aabcdaaxyza','a') FROM dual;--bcdaaxyza
(8)replace(字符串,查找字符串[,替换字符串]):替换字符串中的子串,默认是替换为空字符串
SELECT replace('abcdefgabcd','cd') FROM dual; --abefgab
SELECT replace('abcdefgabcd','cd','#') FROM dual; --ab#efgab#
(9)chr:把编码转换为字符
SELECT chr(65) FROM dual; --A
(10)ascii:把字符转换为编码
SELECT ascii('A') FROM dual; --65
SELECT ascii('国') FROM dual;