database - SQL query to find both 4 character and 6 character values from two tables -

below 2 tables working with, along columns in tables:

 **## table 1 ##**  grantee column w/sample data  grantee ---------- clat  clatx1  djsd  djsdx3  adks  adksx5  dkhs 

 **## table 2 ##**  inact_id column w/sample data  inact_id ---------- clat  djsd  adks  dkhs  

with following query:

select distinct a.grantee            table1                                    exists(select 1                              table2 b              a.grantee= b.inact_id); 

 these results:  clat  djsd  adks  dkhs 

4 character id's returned - not want(also want 6 character id's associated 4 character id's)

in addition getting 4 character id's, of 4 character id's have id following naming convention - a02wxn, x constant, , n number 0-9, 6 character id. want able include 6 character id, if exists along 4 character id. 6 character id's exist in table 1.

update: think might closer solution, hope can clarify more want results.

select a.grantee table1 inner join (select b.inact_id inactive_users b) on a.grantee '%' || b.inact_id || '%';

 (note - done on postgres, , worked, can't run in db2) results postgres below:(desired results - both 4 character id , 6 character id if exists)  grantee -------- clat  clatx1  djsd  djsdx3  adks  adksx5  dkhs 

try below sql :

select distinct a.grantee             table1                                      exists(select 1                               table2 b               substring(a.grantee,1,4)= b.inact_id);

i tried same thing in sybase db. worked fine !

create table #temp1  (  grantee varchar(125)  )  insert #temp1 values('a02w')  insert #temp1 values('a02wx2')  insert #temp1 values('dbds')  insert #temp1 values('dbdsx4')  insert #temp1 values('a03')  insert #temp1 values('askdlakdjd')    create table #temp2  (  inact_id varchar(4)  )  insert #temp2 values('a02w')  insert #temp2 values('aaab')  insert #temp2 values('avcd')  insert #temp2 values('ssld')    select *  #temp1  select *  #temp2      select distinct a.grantee             #temp1                                      exists(select 1                               #temp2 b               substring(a.grantee,1,4) = b.inact_id);