CODEWITHSUNDEEP
sqloracle-databasejoin
eastwater
eastwater

Reputation: 5588

oracle join string to number not working by adding regexp_like

oracle join string to number not working by adding regexp_like. For example,

Foo                           Bar
--------------                ------------
id varchar2(20)               id number(20,0)

Foo id can be number or any string (not number), e.g., 123, hello, world, 555, etc.

select foo.id,bar.id from Foo foo 
left join Bar bar on (regexp_like(foo.id, '^[0-9]+$') and foo.id=bar.id)

error:

ERROR at line 1:
ORA-01722: invalid number

foo.id=bar.id is evaluated only when foo.id is a number, right?

The following is working fine for mysql

select foo.id,bar.id from Foo foo 
left join Bar bar on (foo.id=bar.id)

But it causes the ORA-01722: invalid number for oracle. That is why "regexp_like" is added in join condition.

Upvotes: 2

Views: 163

Answers (2)

William Robertson
William Robertson

Reputation: 16001

Requires Oracle 12.2 or later:

select foo.id,bar.id
from   foo
       left join bar on bar.id = to_number(foo.id default 0 on conversion error);

(I expected it to work with default null on conversion error but it crashes the session.)

Regarding your question about why the original attempt using regexp_like doesn't work, it worked for me in 12.2.0.1. Probably in your case it was evaluating foo.id = bar.id first. It may be possible to hint or rewrite the query to force the regex to be applied first.

Upvotes: 1

VBoka
VBoka

Reputation: 9083

Something like this:

select foo.id
       , bar.id 
from Foo foo 
left join Bar bar on foo.id = to_char(bar.id);

Here is the DEMO

Upvotes: 1

Related Questions