1 字符串连接
SQL> select 'abc' || 'def' from dual;
'ABC'|
------
abcdef
------
abcdef
2 小写
SQL>select lower('ABC012');
lower
--------
abc012
3 大写
select upper('abc012');
upper
--------
ABC012
4 左补全
upper
--------
ABC012
4 左补全
select lpad('abc', 5, '0');
lpad
-------
00abc
select lpad('abc', 5, '012');
lpad
-------
01abc
lpad
-------
00abc
select lpad('abc', 5, '012');
lpad
-------
01abc
第3个参数为空时,缺省为space
select lpad('abc', 5);
lpad
-------
abc
select lpad('abc', 5, ' ');
lpad
-------
abc
lpad
-------
abc
select lpad('abc', 5, ' ');
lpad
-------
abc
5 右补全
select rpad('abc', 5, '0');
rpad
-------
abc00
select rpad('abc', 5, '012');
rpad
-------
abc01
rpad
-------
abc00
select rpad('abc', 5, '012');
rpad
-------
abc01
6 左空白删除
select ltrim(' abc');
ltrim
-------
abc
7 右空白删除
select ltrim('abc ');
ltrim
---------------
abc
ltrim
---------------
abc
8 左右空白删除
select trim(' abc ');
btrim
-------
abc
9 字符串替换
SQL> SELECT TRANSLATE('ababab' , 'a' , '1') FROM DUAL;
TRANSL
------
1b1b1b
------
1b1b1b
10 取子字符串
SQL> select substr('abc012', 3, 2) from dual;
SU
--
c0
--
c0
11 字符串长度
SQL> select length('abc012') from dual;
LENGTH('ABC012')
----------------
6
----------------
6
12 数字转化成字符串
SQL> select to_char(123456, '999,999,999,999') from dual;
TO_CHAR(123456,'
----------------
123,456
----------------
123,456
13 字符串转化为数字
SQL> select to_number('123' || '456', '999999999999') from dual;
TO_NUMBER('123'||'456','999999999999')
--------------------------------------
123456
--------------------------------------
123456
SQL> select to_number('123,456', '999,999,999,999') from dual;
TO_NUMBER('123,456','999,999,999,999')
--------------------------------------
123456
--------------------------------------
123456