SQL 語法雜碎 - 字串處理

SQL 常用字串處理語法說明：包含 substring、left、right、upper、lower、ltrim、rtrim、、、

• 取字串中部分字元
  • 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."

Reference : SQL 字串處理函式 http://technet.microsoft.com/zh-tw/library/ms181984.aspx