字符型
ascii
chr
lower
upper
initcap
concat
substr
length
instr
trim
dump
lpad
rpad
replace
ascii('字符')
返回字符的ascii码值
idle> select ascii('a') from dual;
ascii('a')
----------
97
idle> select ascii('a') from dual;
ascii('a')
----------
65
idle>
chr('n')
返回n的字符值 n是ascii码数
idle> select chr(65) from dual;
c
-
a
idle> select chr(39) from dual;
c
-
'
idle>
但是求单引号的ascii码写法很特殊 两个单引带表一个单引
idle> select ascii(''') from dual;
error:
ora-01756: quoted string not properly terminated
idle> select ascii('''') from dual;
ascii('''')
-----------
39
idle>
lower(列名|表达式)
小写转换
idle> select lower('abc') from dual;
low
---
abc
idle>
dual 为虚表,当一个语句不需要从表中获取数据时,但又要维持sql语法,oracle才提供了虚表来解决这个问题
upper(列名|表达式)
大写转换
idle> select upper('abc') from dual;
upp
---
abc
idle>
initcap(列名|表达式)
每个词的词头大写 其他小写
idle> select initcap('abc def xyz') from dual;
initcap('ab
-----------
abc def xyz
idle>
concat(列名|表达式,列名|表达式)
将第一个字符串和第二个字符串连接
idle> select concat('abc','xyz') from dual;
concat
------
abcxyz
不太常用,因为我们可以用 || 连接
idle> select 'abc'||'xyz' from dual;
'abc'|
------
abcxyz
idle>
substr(列名|表达式,m,[n])
返回指定子串,该子串是从第m个字符开始,其长度为n,不指定n值则从m到最后
idle> select substr('abcdefxyz',4,3) from dual;
sub
---
def
idle> select substr('abcdefxyz',4) from dual;
substr
------
defxyz
idle>
length(列名|表达式)
返回字符串的长度
idle> select length('abcdefxyz') from dual;
length('abcdefxyz')
-------------------
9
idle>
instr (列名|表达式,'字符串',[m],[n])
从表达式或列中搜索给定的字符串的所处位置,m代表从第几个开始搜,n代表第几次出现. m和n默认都是1
idle> select instr('abcdddxyz','d') from dual;
instr('abcdddxyz','d')
----------------------
4
idle>
idle> select instr('abcdddxyz','d',5) from dual;
instr('abcdddxyz','d',5)
------------------------
5
idle> select instr('abcdddxyz','d',5,2) from dual;
instr('abcdddxyz','d',5,2)
--------------------------
6
idle>
trim([leading|]trailing|both 要去掉的字符 from 源字符串)
从源字符串中去掉指定的字符 可以用leading tailing来修饰去掉的字符串是在开头或结尾,默认是两者都 默认截取的是空格
idle> select trim('a' from 'aaabcdeaaafxyzaaa') from dual;
trim('a'fro
-----------
bcdeaaafxyz
idle> select trim(leading 'a' from 'aaabcdeaaafxyzaaa') from dual;
trim(leading'a
--------------
bcdeaaafxyzaaa
idle> select trim(trailing 'a' from 'aaabcdeaaafxyzaaa') from dual;
trim(trailing'
--------------
aaabcdeaaafxyz
idle>
左补齐lpad 右补齐rpad
将不足20个字符的位置用指定符号填充.
idle> select lpad(ename,20,'-') ,rpad(ename,20,'-') from emp where ename like 's%';
lpad(ename,20,'-') rpad(ename,20,'-')
-------------------- --------------------
---------------smith smith---------------
---------------scott scott---------------
idle>
，