0

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?

Record Example

Thanks,

MISNole
  • 992
  • 1
  • 22
  • 48

2 Answers2

1

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

AngocA
  • 7,655
  • 6
  • 39
  • 55
  • Note if all you need is the blanks removed from the left then use LTRIM() as that will be less overhead than TRIM(). Same applies to blanks on the right; you'd want to use RTRIM() instead of TRIM(). Only use TRIM() if you really need blanks from both sides removed. – Charles Feb 24 '15 at 15:35
0

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'
 ) );
Charles
  • 21,637
  • 1
  • 20
  • 44