找出最後一碼為小寫字母之資料

MSSQL

方法(1)：用ASCII函數取得小寫字母值域。

方法(2)：LIKE指令，搜尋中括號所列任一之小寫字母。

方法(3)：PATINDEX，與LIKE雷同。

SELECT Crncy

, RIGHT(Crncy, 1)

, CASE WHEN ASCII(RIGHT(Crncy, 1)) BETWEEN 96 AND 123 THEN 1 ELSE 0 END        "M-1"

, CASE WHEN Crncy COLLATE Latin1_General_CS_AI LIKE '%[abcdefghijklmnopqrestuvwxyz]'

THEN 1 ELSE 0 END "M-2"

, PATINDEX('%[abcdefghijklmnopqrestuvwxyz]%', Crncy COLLATE Latin1_General_CS_AI) "M-3" 

FROM

    (VALUES('UsD'), ('EUR'), ('BRL'), ('GBp')) AS Ex(Crncy)

ORACLE

方法(1)：用ASCII函數取得小寫字母值域。

方法(2)：REGEXP_INSTR

SELECT COLUMN_VALUE

   , SUBSTR(COLUMN_VALUE, -1) BACKEND

   , CASE WHEN ASCII(SUBSTR(COLUMN_VALUE, -1)) BETWEEN 97 AND 122 THEN 1 ELSE 0 END "M-1"

   , REGEXP_INSTR(COLUMN_VALUE, '[[:lower:]]$') "M-2a"

   , REGEXP_INSTR(COLUMN_VALUE, '[a-z]$') "M-2b"

FROM TABLE(SPLIT_TBL('UsD','EUR','BRL','GBp'))

註: CREATE OR REPLACE TYPE SPLIT_TBL AS TABLE OF VARCHAR2(32767)