Trimming Blank Spaces in Char Column in DB2

Question

I'm trying to remove blank spaces that appear in a CHAR column within DB2. I received some helped here with the function TRANSLATE to determine if Left contained records that began with three letters:

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

But as you can see in my screenshot, there are records that remain, presumably because they begin with a blank space. I tried cast (hos.hpid as varchar) but that doesn't work. Is it possible to trim these blank spaces?

Thanks,

Answer 1

Use LTRIM() or TRIM() to trim blanks before the LEFT()

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; 

Note that the use of such functions in the WHERE clause means that performance is going to take a hit. At minimum, the query engine will have to do a full index scan; it may do a full table scan.

If this is a one time thing or a small table, it's not a big deal. But if you need to do this often on a big table look to see if your platform and version of DB2 supports expressions in indexes...

create index myindex on hospidl1 
  ( TRANSLATE( 
LEFT( TRIM(hpid), 3 ),
'AAAAAAAAAAAAAAAAAAAAAAAAA', 
'BCDEFGHIJKLMNOPQRSTUVWXYZ'
 ) );

Answer 2

In recent versions of db2, you can also use just trim() to remove blanks from both sides.