I got this little sql-statement:
select
NVL(table2.usermail, 'some other mail') user_mail
from
table1, table2
where table1.user = table2.usernr
This works pretty fine but the result isnt the one I need.
Example:
table1.user gives me "KNL\MarkZuckerbeg"
table2.usernr gives me "MarkZuckerbeg"
table1.user = table2.usernr now is the reason I dont get data back.
Any ideas how to solve this?
I tried using INSTR with no results.
You can use like instead:
select coalesce(t2.usermail, 'some other mail') as user_mail
from table1 t1 join
table2 t2
on t1.user like '%' || t2.usernr || '%';
Note other changes:
JOIN syntax. Simple rule: Never use commas in the FROM clause.COALESCE() instead of NVL(). COALESCE() is the ANSI standard function and more flexibly (it can take more than two arguments).You can use INSTR if you wish to:
SELECT NVL(TABLE2.USERMAIL, 'some other mail') USER_MAIL
FROM TABLE2
INNER JOIN TABLE1
ON INSTR(TABLE1.USER, TABLE2.USERNR) > 0 OR
INSTR(TABLE2.USERNR, TABLE1.USER) > 0
Best of luck.
