SQL 常用字串處理語法說明

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."

取除特定字串及刪除右邊字串
語法：replace(欄位 COLLATE Chinese_Taiwan_Stroke_BIN2 ,'特定字元',char(0))
ex:
ABC-123 ，希望可變成 ABC
replace(Fac.InfoCode COLLATE Chinese_Taiwan_Stroke_BIN2,  '-',char(0))

或


找出有符合字串'-'，回傳數值再減1，即是連'-'也不取
PATINDEX可判斷是否有要查詢之字串(有回傳1沒有0)

select case when PATINDEX('%-%',name) > 0
then substring(name,1,CHARINDEX('-',name,1)-1)
else name end as name
from sys_user,frm_in