oracle 字符串 补,ORACLE 字符串操作 (备忘)

1 字符串连接

SQL> select 'abc' || 'def' from dual;

'ABC'|

------

abcdef

2　小写

SQL>select lower('ABC012');

lower

--------

abc012

3　大写

select upper('abc012');

upper

--------

ABC012

4　 左补全

select lpad('abc', 5, '0');

lpad

-------

00abc

select lpad('abc', 5, '012');

lpad

-------

01abc

第3个参数为空时，缺省为space

select lpad('abc', 5);

lpad

-------

abc

select lpad('abc', 5, ' ');

lpad

-------

abc

5 　右补全

select rpad('abc', 5, '0');

rpad

-------

abc00

select rpad('abc', 5, '012');

rpad

-------

abc01

6　左空白删除

select ltrim('          abc');

ltrim

-------

abc

7　右空白删除

select ltrim('abc          ');

ltrim

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

abc

8　左右空白删除

select trim('          abc          ');

btrim

-------

abc

9　字符串替换

SQL> SELECT TRANSLATE('ababab' , 'a' , '1') FROM DUAL;

TRANSL

------

1b1b1b

10　取子字符串

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

SU

--

c0

11　字符串长度

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

LENGTH('ABC012')

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

6

12　 数字转化成字符串

SQL> select to_char(123456, '999,999,999,999') from dual;

TO_CHAR(123456,'

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

123,456

13　字符串转化为数字

SQL> select to_number('123' || '456', '999999999999') from dual;

TO_NUMBER('123'||'456','999999999999')

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

123456

SQL> select to_number('123,456', '999,999,999,999') from dual;

TO_NUMBER('123,456','999,999,999,999')

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

123456