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