How to filter data from 2 different tables in SQL but the other table has few strings to ignore?-CodePudding

For example:

One table has account name such as Apple, Google, Facebook.
Other table has account name as 2-apple,3-google,4-facebook.

I need to combine those 2 tables based on account name but want the query to ignore the first 2 string values from the other table = bolded values(2-apple, 3-google, 4-facebook).

CodePudding user response：

In Oracle (as you tagged it), you'd use SUBSTR function:

select whatever
from t1 join t2 on t1.name = substr(t2.name, 3)

as it returns what you wanted, e.g.

SQL> select substr('2-apple', 3) result from dual;

RESUL
-----
apple

SQL>

CodePudding user response：

Keeping in mind lowercase to avoid problems with the comparison

with x as ( 
select 'Google' as mark from dual union all
select 'Facebook' as mark from dual 
),
y as (
select '2-apple' as other from dual union all 
select '3-google' as other from dual union all
select '4-facebook' as other from dual 
) 
select x.mark, y.other 
from x inner join y 
 on ( lower(x.mark) = lower(substr(y.other,3)) );

Result

SQL> with x as (
select 'Google' as mark from dual union all
select 'Facebook' as mark from dual
  ),
  y as (
select '2-apple' as other from dual union all
  select '3-google' as other from dual union all
select '4-facebook' as other from dual
)
   select x.mark, y.other
from x inner join y
    on ( lower(x.mark) = lower(substr(y.other,3)) );

MARK     OTHER
-------- ----------
Google   3-google
Facebook 4-facebook