SQL function to filter out column values that start with 'ABC' or any three letters

Question

I'm trying to adapt an SQL query to filter out any rows where the value in a specific column begins with three letters. For example, in the screenshot shown below, the only row I want the query to return is row 11: (13, '0511:15', 'FT20'). Is there a function in DB2 that I can use to accomplish this?

My simple query is:

select pat.f1, hos.hpid, hos.hpcd
from patall3 pat
join hospidl1 hos on pat.f1=hos.hpacct
order by pat.f1;

I'm not familiar at all with DB2 but I hope there is a way to accomplish this. Any suggestions?

enter image description here

You could use an XPath `matches` function, as described [here](http://stackoverflow.com/questions/4763757/regular-expressions-in-db2-sql). The regular expression in your case might look like `^[a-zA-Z]{3}`. — mustaccio, Feb 24 '15 at 02:38

Fred Sobotka · Accepted Answer · 2015-02-25T05:40:52.587

You could use the TRANSLATE function to determine if LEFT( LTRIM( hos.hpid ), 3 ) contains letters.

Calling the TRANSLATE function in the following manner replaces all uppercase capital letters from the input string with the letter A:

TRANSLATE( inputString, 
   'AAAAAAAAAAAAAAAAAAAAAAAAA', 
   'BCDEFGHIJKLMNOPQRSTUVWXYZ'
)

Here's how to restrict the TRANSLATE function to evaluate just the first three characters of the column:

select pat.f1, hos.hpid, hos.hpcd
from patall3 pat
join hospidl1 hos on pat.f1=hos.hpacct
where TRANSLATE( 
    LEFT( LTRIM( hos.hpid ), 3 ),
    'AAAAAAAAAAAAAAAAAAAAAAAAA', 
    'BCDEFGHIJKLMNOPQRSTUVWXYZ'
 ) <> 'AAA'
order by pat.f1;
;

That query will only block values that contain letters for all of the first three characters ('AAA', 'AAB', 'AAC', etc.). If you'd rather block rows that contain a letter in any of the first three characters, then use this query instead:

select pat.f1, hos.hpid, hos.hpcd
from patall3 pat
join hospidl1 hos on pat.f1=hos.hpacct
where TRANSLATE( 
    LEFT( LTRIM( hos.hpid ), 3 ),
    'AAAAAAAAAAAAAAAAAAAAAAAAA', 
    'BCDEFGHIJKLMNOPQRSTUVWXYZ'
 ) NOT LIKE '%A%'
order by pat.f1;
;

Thanks Fred. Looks like that is almost there. If there are blank spaces before the record, for example ' TFC2186', this does not remove them. Is there a way to strip off all blank spaces before this TRANSLATE is applied? — MISNole, Feb 24 '15 at 13:44
Yes. I've revised my answer to show how the `LTRIM` and `LEFT` functions can be combined. — Fred Sobotka, Feb 25 '15 at 05:42

SQL function to filter out column values that start with 'ABC' or any three letters

1 Answers1