Search code examples
informixalpha

is there are any alpha logic define in informix DB

i am using informix DB , i need to get records that contain alpha [A-Za-z] character on last character what i try is : 

select * from table_name 
where (SUBSTR(trim(customer),-1,1)!='0' and SUBSTR(trim(customer),-1,1)!='1' and SUBSTR(trim(customer),-1,1)!='2' and SUBSTR(trim(customer),-1,1)!='3' and SUBSTR(trim(customer),-1,1)!='4' and SUBSTR(trim(customer),-1,1)!='5' and SUBSTR(trim(customer),-1,1)!='6' and SUBSTR(trim(customer),-1,1)!='7' and SUBSTR(trim(customer),-1,1)!='8' and SUBSTR(trim(customer),-1,1)!='9') or (SUBSTR(trim(customer),-1,1)=' ') or (SUBSTR(trim(customer),-1,1)='') or (customer IS NULL)

is there are any way to write where SUBSTR(trim(customer),-1,1)=alpha rather than write SUBSTR(trim(customer),-1,1)!='0' and SUBSTR(trim(customer),-1,1)!='1' and SUBSTR(trim(customer),-1,1)!='2' and SUBSTR(trim(customer),-1,1)!='3' and SUBSTR(trim(customer),-1,1)!='4' and SUBSTR(trim(customer),-1,1)!='5' and SUBSTR(trim(customer),-1,1)!='6' and SUBSTR(trim(customer),-1,1)!='7' and SUBSTR(trim(customer),-1,1)!='8' and SUBSTR(trim(customer),-1,1)!='9'

Solution

  • If you have a 'recent' version of Informix (anything over 12.10 should do) you can use regex_match():

    https://www.ibm.com/support/knowledgecenter/SSGU8G_14.1.0/com.ibm.dbext.doc/ids_dbxt_544.htm

    Something like :

    > select * from table(set{'test','test1','tesT'})
  where regex_match(unnamed_col_1, '[a-zA-Z]$');

unnamed_col_1

test
tesT

2 row(s) retrieved.

>