[Oracle]Oracle SQL内置函数大全(一)

8.UPPER

返回字符串,并将所有的字符大写

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

UPPER

--------

AABBCCDD

9.RPAD和LPAD(粘贴字符)

RPAD 在列的右边粘贴字符

LPAD 在列的左边粘贴字符

SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;

LPAD(RPAD('GAO',1

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

*******gao*******

不够字符则用*来填满

10.LTRIM和RTRIM

LTRIM 删除左边出现的字符串

RTRIM 删除右边出现的字符串

SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;

LTRIM(RTRIM('

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

gao qian jing

11.SUBSTR(string,start,count)

取子字符串,从start开始,取count个

SQL> select substr('13088888888',3,8) from dual;

SUBSTR('

--------

08888888

12.REPLACE('string','s1','s2')

string 希望被替换的字符或变量

s1 被替换的字符串

s2 要替换的字符串

SQL> select replace('he love you','he','i') from dual;

REPLACE('HELOVEYOU','HE','I')

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

i love you

13.SOUNDEX

返回一个与给定的字符串读音相同的字符串

SQL> create table table1(xm varchar(8));

SQL> insert into table1 values('weather');

SQL> insert into table1 values('wether');

SQL> insert into table1 values('gao');

SQL> select xm from table1 where soundex(xm)=soundex('weather');

XM

--------

weather

wether

14.TRIM('s' from 'string')

LEADING 剪掉前面的字符

TRAILING 剪掉后面的字符

如果不指定,默认为空格符