oracle 字符串匹配函数,oracle常用字符处理函数总结

1. ASCII(c1) c1为字符串返回一个字符的ASCII码

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

ASCII('A') ASCII('A')

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

65         97

2. CHR(i) 是ASCII的逆函数，i只能是一个数字

SQL> select chr(65), chr(97)from dual;

C C

- -

A a

若()内不是一个数字会报出ORA-01722: invalid number的信息

SQL> select chr('a') from dual;

select chr('a') from dual

*

ERROR at line 1:

ORA-01722: invalid number

3. CONCAT(c1,c2) c1，c2都为字符串，将两个字符串连接起来，与连接符"||"相同

SQL> select concat('hel','lo') from dual;

CONCA

-----

hello

SQL> select 'hel' || 'lo' from dual;

'HEL'

-----

hello

若()字符串的数量大于2会下列错误，因此concat()函数只能连接两个字符串

SQL> select concat('a','b','c') from dual

select concat('a','b','c') from dual

*

ERROR at line 1:

ORA-00909: invalid number of arguments

4. INITCAP(c1) c1为字符串，该函数将每个单词的第一个之母的字母转换成大写，其它的之母小写返回，通过下面例子可以看出该函数以标点符号和特殊字符为分隔符。

SQL> select initcap('jerry,henry,jack') from dual;

INITCAP('JERRY,H

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

Jerry,Henry,Jack

SQL> select initcap('JeRRY,HENRY,jACK') from dual;

INITCAP('JERRY,H

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

Jerry,Henry,Jack

SQL> select initcap('jerry henry jack') from dual;

INITCAP('JERRYHE

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

Jerry Henry Jack

SQL> select initcap('jerry.henry.jack') from dual;

INITCAP('JERRY.H

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

Jerry.Henry.Jack

SQL> select initcap('jerry\henry\jack') from dual;

INITCAP('JERRY\H

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

Jerry\Henry\Jack

SQL> select initcap('jerry-henry-jack') from dual;

INITCAP('JERRY-H

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

Jerry-Henry-Jack

SQL> select initcap('jerry$henry$jack') from dual;

INITCAP('JERRY$H

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

Jerry$Henry$Jack

SQL> select initcap('jerry*henry*jack') from dual;

INITCAP('JERRY*H

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

Jerry*Henry*Jack

5. INSTR(c1,c2,[,i[,j]]) 其中c1,c2均为字符串，i，j为整数，该函数返回c2在c1中第j次出现的位置，搜索从c1的第i个字符开始。当没有发现所需要的字符时，则返回为0；若果i为负数，那么搜索从右到左开始，但是位置的计算还是从左到右，i和j的默认值为1

由于默认i和j的值都为1，并且默认从左往右边搜索，因此下面返回为2

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

INSTR('DATABASE','A')

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

2

SQL> select instr('database','a',1,1) from dual;

INSTR('DATABASE','A',1,1)

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

2

将i更改为-1，从右往左边查找，由于位置的计算还是从左到右边，因此下面返回为6而不是3

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

INSTR('DATABASE','A',-1,1)

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

6

从第五个字符开始搜索，返回为6

SQL> select instr('database','a',5) from dual;

INSTR('DATABASE','A',5)

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

6

从第五个字符开始搜索，查找第二个匹配a的值，可以看出database中第5个字符后面只有一个2，没有发现所需要的字符，因此返回为0

SQL> select instr('database','a',5,2) from dual;

INSTR('DATABASE','A',5,2)

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

0

从第三个字符开搜索，这里匹配条件成功返回

SQL> select instr('database','a',3,2) from dual;

INSTR('DATABASE','A',3,2)

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

6

5.instrb(1,c2,[,i[,j]])

与instr()函数一样，只是这里返回的是字节，对于单字节，instrb()效果等于instr()

6. LENGTH(c1)如果c1为字符串，则返回c1的长度；若果c1为null，那么将返回null值

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

LENGTH('ABC')

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

3

SQL> select length(' ') from dual;

LENGTH('')

----------

1

SQL> select length('') from dual;

LENGTH('')

----------

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

LENGTH('123')

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

3

SQL> select length(123) from dual;

LENGTH(123)

-----------

3

8. LENGTHB(c1)

与length()一样，返回字节

9. LOWER(c1) 返回c1的小写字符

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

LOWER

-----

hello

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

LOWER

-----

hello

10 upper(c1) 返回c1的大写

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

UPPER

-----

HELLO

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

UPPER

-----

HELLO

11. LPAD(c1,i,c2) c1,c2均为字符串，i为整数，该函数用来在c1的左侧用c2字符串补足i，如果i小于c1的长度，那么只返回长度为i的c1字符，其它的被截去，c2的默认值为单空格

下面例子7小于字符串database的长度，因此只返回database前7个字符

SQL> select lpad('database','7','$') from dual;

LPAD('D

-------

databas

下面例子，填充了连个$符号

SQL> select lpad('database','10','$') from dual;

LPAD('DATA

----------

$$database

若不指定填充的字符，默认使用空格，因此下面例子在database前面填充了2个空格

SQL> select lpad('database','10') from dual;

LPAD('DATA

----------

database

验证是否为10个字符

SQL> select length(lpad('database','10')) from dual;

LENGTH(LPAD('DATABASE','10'))

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

10

12. RPAD(c1,i,c2) 与LPAD相反，在c1的右边填充

SQL> select rpad('database','10','$') from dual;

RPAD('DATA

----------

database$$

SQL>

13. LRIM(c1,c2) 将c1左边字符去掉，使其第一个字符不在c2中，如果没有c2，则c1就不会改变

这个好像有点不好理解，我试了很久，终于弄明白啦，下面的例子从database字符串的左边开始搜索，只要第一个字符匹配了dta中的任意一个，就截去，然后再看第二个字符，第三个....当查找到第5个字符b的时候发现b不包含在dta中，这时候查找结束，返回不匹配的字符开始到结束的字符串

SQL> select ltrim('database','dta') from dual;

LTRI

----

base

如果包上面的dta改成bdta会是什么样呢，根据上面的总结，可想而知只会返回se，看结果

SQL> select ltrim('database','bdta') from dual;

LT

--

se

14. TRIM(c1 from c2) 该函数用于从字符串的头部、尾部或两端截特定字符，参数c1为要截去的字符，c2为原字符串

15. REPALCE(c1,[c2,c3]) c1,c2,c3都为字符串，将c1里面含有c2的字符串替换成c3

SQL> select replace('updodown','up','down') from dual;

REPLACE('U

----------

downdodown

16. substr(c1,i[,j]) c1为字符串，i，j为整数从第i位开始返回长度为j的字符串，如果j为空则直到串的尾部

SQL> select substr('database',5) from dual;

SUBS

----

base

SQL> select substr('database',5,3) from dual;

SUB

---

bas

17. TRANSLATE(c1,c2,c3) 将c1中与c2相同的字符以c3代替，

这个看起来与replace一样，translate()返回结果只是把d提换成m了，而不是mb

SQL> select translate('database','d','mb') from dual;

TRANSLAT

--------

matabase

而replace()是吧d替换成mb了

SQL> select replace('database','d','mb') from dual;

REPLACE('

---------

mbatabase

再看下面例子就明白了，把匹配da的替换成mb了，da是一个整体

SQL> select replace('database','da','mb') from dual;

REPLACE(

--------

mbtabase

将d用m替换，a用b替换，可以看出匹配的是单个字符，而且每个字符的位置一一对应。

SQL> select translate('database','da','mb') from dual;

TRANSLAT

--------

mbtbbbse