字符类型函数

字符型

ASCII

CHR

chr(39) chr(10) chr(9)

LOWER

UPPER

INITCAP 

CONCAT

||

SUBSTR

LENGTH

INSTR

TRIM

dump

lpad

rpad

REPLACE

translate

B19306 books  sql 第5章 functions

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> 

REPLACE(源字符串,OLD字符串,NEW字符串)

从源字符串中找到搜索的old字符串,替换成new字符串

idle> select replace('abcdefxyz','def','DEF') from dual;

REPLACE('

---------

abcDEFxyz

idle> 

dump('str'[,FMT[,S,E]])

用于转换进制格式

str 被转换的字符串

FMT 格式

S   str的开始字符位置

E   S开始的后续结束字符位置

SQL> select dump('abcd') from dual;

DUMP('ABCD')

--------------------------

Typ=96 Len=4: 97,98,99,100

SQL>

SQL> select dump('abcd',1002) from dual;

DUMP('ABCD',1002)

------------------------------------------------

Typ=96 Len=4 CharacterSet=AL32UTF8: 97,98,99,100

SQL>

SQL> select dump('abcd',1008) from dual;

DUMP('ABCD',1008)

---------------------------------------------------

Typ=96 Len=4 CharacterSet=AL32UTF8: 141,142,143,144

SQL>

DUMP('ABCD',1016)

-----------------------------------------------

Typ=96 Len=4 CharacterSet=AL32UTF8: 61,62,63,64

SQL> select dump('abcd',1017) from dual;

DUMP('ABCD',1017)

-------------------------------------------

Typ=96 Len=4 CharacterSet=AL32UTF8: a,b,c,d

SQL> 

SQL> select dump('abcd',1017,2,2) from dual;

DUMP('ABCD',1017,2,2)

---------------------------------------

Typ=96 Len=4 CharacterSet=AL32UTF8: b,c

SQL> 

SQL> select dump('飞') from dual;

DUMP('飞')

-------------------------

Typ=96 Len=3: 233,163,158

SQL> select 233*power(256,2)+163*256+158 from dual;

233*POWER(256,2)+163*256+158

----------------------------

    15311774

SQL> select chr(15311774) from dual;

CHR

---

飞

SQL> 

单行函数的例子

idle> select concat(ename,job) "EMP",substr(job,1,5) title,length(ename),instr(ename,'T',1,2)  

from emp 

where lower(ename) = 'scott';

EMP     TITLE LENGTH(ENAME) INSTR(ENAME,'T',1,2)

------------------- ----- ------------- --------------------

SCOTTANALYST     ANALY       5      5

idle>