【SQL】字符型函数

1、 ASCII

ASCII（American Standard Code for Information Interchange，美国信息交换标准代码）是基于拉丁字母的一套电脑编码系统。

1） 返回与指定字符对应的ASCII码

SQL> select ascii('A') A,ascii('a') "a",ascii('0') zero,ascii(' ') space from dual;

         A          a       ZERO      SPACE
---------- ---------- ---------- ----------
        65         97         48         32

2） 返回单引号的ASCII码

SQL> select ascii('''') from dual;

ASCII('''')
-----------
         39

3） ASCII只针对第一个字母进行转换

SQL> select ascii('A'),ascii('AB'),ascii('ABC') from dual;

ASCII('A') ASCII('AB') ASCII('ABC')
---------- ----------- ------------
        65          65           65

2、 CHR

语法：CHR（n [USING NCHAR_CS]）

其中 “n” 为ASCII码数

SQL> select chr(39) from dual;

C
-
'

注：CHR(13) 为回车； CHR(10) 为换行。

3、 CONCAT

语法：CONCAT(char,dest_char_set[, source_char_set ])

作用：连接字符串，只能连接两个字符串

SQL> select concat('HELLO'||chr(32),'WORLD')||'!' as "HELLO WORLD" from dual;

HELLO WORLD
------------
HELLO WORLD!

4、 INITCAP

语法：INITCAP(char)

作用：返回字符串并将首字母大写

SQL> select initcap('hello') "Hello" from dual;

Hello
-----
Hello

5、 INSTR

语法：INSTR(string1，string2，start_position,nth_appearance)

string1:被检索的字符串

string2:希望搜索的字符串

start_position:搜索开始的位置，默认为1。如果为正，则为从左到右检索；如果为负，则为从右到左检索。

nth_appearance:代表要查找第几次出现的string2. 此参数可选，如果省略，默认为 1，如果为负数系统会报错。

作用：在一个字符串中搜索指定的字符，返回发现指定的字符位置

SQL> select instr('a2a4a6a8','a') "instr" from dual;

     instr
----------
         1

SQL> select instr('a2a4a6a8','a',-1,1) "instr" from dual;

     instr
----------
         7

SQL> select instr('a2a4a6a8','a',2,2) "instr" from dual;

     instr
----------
         5

6、 SUBSTR

语法：substr（string，start_position，[length]）

作用：取子字符串，从start_position开始，取length长度个字符，如果length缺省，表示截取到字符串末尾。

1） 字符串‘a2a4a6a8’,从后面数第一个‘a’开始，截取之后的所有字符串

SQL> select substr('a2a4a6a8',7) from dual;

SU
--
a8

2） 字符串‘a2a4a6a8’,从第2个字符开始，截取3个字符

SQL> select substr('a2a4a6a8',2,3) from dual;

SUB
---
2a4

7、 VSIZE

语法：VSIZE(expr）

作用：返回expr的内部字节数，如果expr为空，返回null值

SQL> select vsize('hello') from dual;

VSIZE('HELLO')
--------------
             5

8、 LENGTH

语法：LENGTH（expr）

作用：返回expr的字符长度

SQL> select length('hello') from dual;

LENGTH('HELLO')
---------------
          5

9、 LOWER

语法:LOWER(char)

作用：返回字符串，并将所有的字母小写

SQL> select lower('HELLO') from dual;

LOWER
-----
hello

10、 UPPER

语法：UPPER(char)

作用：返回字符串，并将所有字母大写

SQL> select upper('hello') from dual;

UPPER
-----
HELLO

11、 LPAD&RPAD

语法：

LPAD(string,padded_length,[pad_string])

RPAD(string,padded_length,[pad_string])

string

表示：被填充的字符串

padded_length

表示：字节的长度，是返回的字节长度的字符串，如果这个数量比原字符串的字节长度要短，rpad/lpad函数将会把字符串截取成从左到右的n个字符;如果正好是中文要被截断，那么这个中文返回的是空格。如：rpad('函数',3) ----返回的是'函 '。

pad_string

是个可选参数，这个字符串是要粘贴到string的右边，如果这个参数未写，rpad函数将会在string的右边粘贴空格，lpad函数将会在string的左边粘贴空格。
1）用字符“0”使字符串在列的左侧补足5位
SQL> select lpad(rownum,5,'0') as left from dual connect by rownum<=5;

LEFT
--------------------
00001
00002
00003
00004
00005
2）用字符“0”使字符串在列的左侧补足6位，然后再在右侧用“0”补足到9位

SQL> select rpad(lpad(rownum,6,'0'),9,'0') as lr from dual connect by rownum <=5;

LR
------------------------------------
000001000
000002000
000003000
000004000
000005000

12、 LTRIM & RTRIM

语法：
LTRIM (string [, string_appearance]) RTRIM (string [, string_appearance])
作用：删除string 左/右边出现的字符（string_appearence）,当string_appearence缺省时删除左/右边出现的空格。

1） 删除字符串‘000100’左边的‘0’
SQL> select ltrim('000100','0') from dual;

LTR
---
100
2） 删除字符串‘000100’右边的‘0’
SQL> select rtrim('000100','0') from dual;

RTRI
----
0001
3） 删除字符串‘   10’左边的空格
SQL> select ltrim('  10') from dual;

LT
--
10

13、 REPLACE

语法：REPLACE(string，string1，string2）
string：希望被替换的字符或变量
string1：被替换的字符串
string2：要替换的字符串，缺省值为null
作用：用string2替换string中出现的string1
1）将字符串的‘0’替换为null
SQL> select replace('000100','0') from dual;

R
-
1

2）将字符串的‘0’替换为‘1’
SQL> select replace('000100','0','1') from dual;

REPLAC
------
111111

14、 TRANSLATE

语法：TRANSLATE(string，from_string,to_string)
作用：返回将出现在from_string中的每个字符替换为to_string中的相应字符以后的字符串。若from_string比to_string字符串长，那么在from_string中比to_string中多出来的字符将会被删除，三个参数中有一个是空，返回值也将是空值。
1）
SQL> select translate('abclloc','abc','he') from dual;

TRANS
-----
hello

2）返回‘，’的个数
SQL> select length(translate('a,b,c,d',',abcd',',')) from dual;

LENGTH(TRANSLATE('A,B,C,D',',ABCD',','))
----------------------------------------
                                       3

3）将数字保留，将其他的大写字母移除
SQL> SELECT TRANSLATE('2KRW229','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789') "Translate example" FROM DUAL;

Tran
----
2229

15、 TRIM

语法：TRIM([{{LEADING | TRAILNG | BOTH}
           [trim_character]
          | trim_character} FROM ] trim_source)
LEADING:剪掉前面的字符
TRAILING:剪掉后面的字符
BOTH:前后都减

1） 默认 （等同于BOTH）
SQL> with t as (select chr(32)||'OCP'||chr(32) as name from dual)
  2  select '|'||name||'|','|'||trim(name)||'|' from t;

'|'||NA '|'||
------- -----
| OCP | |OCP|

2) 剪掉前面的空格
SQL> with t as (select chr(32)||'OCP'||chr(32) as name from dual)
  2  select '|'||name||'|','|'||trim(leading from name)||'|' from t;

'|'||NA '|'||T
------- ------
| OCP | |OCP |

3） 剪掉后面的空格
SQL> with t as (select chr(32)||'OCP'||chr(32) as name from dual)
  2  select '|'||name||'|','|'||trim(trailing from name)||'|' from t;

'|'||NA '|'||T
------- ------
| OCP | | OCP|

4） 剪掉指定字符“P”
SQL> with t as (select chr(32)||'OCP'||chr(32) as name from dual)
  2  select trim(upper('p') from trim(name))||'M' from t;

TRI
---
OCM

5） 剪掉后面的字符“P”
SQL> with t as (select chr(32)||'OCP'||chr(32) as name from dual)
  2  select trim(trailing upper('p') from trim(name))||'M' from t;

TRI
---
OCM