SQL 常用字串處理語法

取字串中部分字元

SELECT Mem_ID, substring(Mem_ID,4,6) AS MEM_ID_6 

FROM members

語法：substring(欄位, 起始字元, 取幾字元)

ex: A123456789  => substring(Id,4,6) => 456789

取左側字元

SELECT LEFT(engname,5) 

FROM members

語法：left(欄位, 位數)

ex: SELECT LEFT('abcdefghijk',5) => abcde

取右側字元

SELECT RIGHT(engname,5) 

FROM members

語法：right(欄位, 位數)

ex: SELECT RIGHT('abcdefghijk',5) => ghijk

字串大小寫轉換 upper、lower

SELECT upper(engname) 

FROM members

語法：upper(欄位）

ex: michael => upper => MICHAEL

SELECT lower(engname) 

FROM members

語法：lower(欄位）

ex: MICHAEL => lower => michael

去除無謂空白 ltrim、rtrim

SELECT LTRIM(email) 

FROM members

語法：ltrim(欄位)

ex: "    abd@com" => ltrim => "abd@com"

SELECT RTRIM(memo) 

FROM members

語法：rtrim(欄位)

ex:"Have a nice day.     " => rtrim =>"Have a nice day."