Sometimes I am asked a question by a colleague I think would be a good example to share in this blog. This week I was asked was there a way in a SQL statement to only return the records from a file where the last three positions are not numeric. For example:

A2CDEF = Include

= Include ABC4EF = Exclude

There is an added complication as the string I need to test is contained within a ten long character field, but it can be of any length 1 - 10.

I quickly made a test file, TESTFILE , that I can use for these examples. The file contains the following records:

FLD001 ---------- ABCDEFGHIJ KLMNOP QRS1234567 TUV8W9X0Y 1Z2

I have explained in a previous post how to get the rightmost characters in a string, using SQL's RIGHT scalar function. Using this I can easily get the last three characters of every field:

SELECT FLD001,RIGHT(RTRIM(FLD001),3) FROM TESTFILE

Which gives me:

FLD001 RIGHT ---------- ----- ABCDEFGHIJ HIJ KLMNOP NOP QRS1234567 567 TUV8W9X0Y X0Y 1Z2 1Z2

Note: The fourth result is "X"-zero-"Y"

Testing the last three characters are numeric is a rare example of where Db2 for i falls short.

Other SQL databases have a regular expression that would be perfect for this kind of a test:

RIGHT(RTRIM(FLD001),3) NOT LIKE '%[0-9]%'

Alas, in Db2 I have to check each position in the last three characters if it is between 'A' and 'Z':

SELECT FLD001,RIGHT(RTRIM(FLD001),3) FROM TESTFILE WHERE SUBSTRING(RIGHT(RTRIM(FLD001),3),1,1) BETWEEN 'A' AND 'Z' AND SUBSTRING(RIGHT(RTRIM(FLD001),3),2,1) BETWEEN 'A' AND 'Z' AND SUBSTRING(RIGHT(RTRIM(FLD001),3),3,1) BETWEEN 'A' AND 'Z'

Only two records are returned in the results:

FLD001 RIGHT ---------- ----- ABCDEFGHIJ HIJ KLMNOP NOP

If I wanted to return all records where any of the last three characters contain a number I would replace the AND in the WHERE clause with OR .

SELECT FLD001,RIGHT(RTRIM(FLD001),3) FROM TESTFILE WHERE SUBSTRING(RIGHT(RTRIM(FLD001),3),1,1) BETWEEN 'A' AND 'Z' OR SUBSTRING(RIGHT(RTRIM(FLD001),3),2,1) BETWEEN 'A' AND 'Z' OR SUBSTRING(RIGHT(RTRIM(FLD001),3),3,1) BETWEEN 'A' AND 'Z'

Which returns to me a few more rows in the results.

FLD001 RIGHT ---------- ----- ABCDEFGHIJ HIJ KLMNOP NOP TUV8W9X0Y X0Y 1Z2 1Z2

This article was written for IBM i 7.3, and should work for some earlier releases too.