Oracle join von string mit number

[BaseBallBatBoy]

Hallo.

Ich habe zwei Tabellen tab_a und tab_b. A hat id als sting, wobei einige Zahlen und andere Character sind. B hat id als number. Die zwei Spalten sollten sich nach einer Einschränkung auf A (nur die Zahlen) eigentlich joinen lassen. Aber ich kriege den Fehler ORA-01722: invalid number.

SELECT
  a.id, b.id
FROM
  (SELECT to_number(id) as id FROM tab_a WHERE REGEXP_LIKE(id,'^-?[[:digit:],.]*$') ) a
INNER JOIN
  tab_b b
ON a.id = b.id;

Hat jemand eine Idee was ich hier vergesse?

 

[Biber3]

Moin BaseBallBatBoy,

dein RegExp-Check ist zu tolerant. Da bleiben eben zuviele "Niht-Zahlen" übrig.

Beispiel:

with a(id) as ( 
  select '47a' as id from dual union all
  select '0.7' as id from dual union all
  select NULL as id from dual union all
  select '0,8' as id from dual union all
  select '0,8,,,' as id from dual union all
  select '1x1' as id from dual union all
  select '-.' as id from dual union all
  select '1...0' as id from dual union all
  select '08sjfg' as id from dual union all
  select '15:30h' as id from dual )
select '''' ||a.id ||'''' as idtxt
, case when regexp_like(a.id, '^-?[0-9]*\,?[0-9]*$')
  then 'Zahl' else 'keine Zahl' end as mycheck
, case when regexp_like(a.id, '^-?[[:digit:],.]*$')
  then 'Zahl' else 'keine Zahl' end as dycheck
from a
;

Ausgabe:
IDTXT | MYCHECK | DYCHECK
---------+------------+-----------
'47a' | keine Zahl | keine Zahl
'0.7' | keine Zahl | Zahl
'' | keine Zahl | keine Zahl
'0,8' | Zahl | Zahl
'0,8,,,' | keine Zahl | Zahl
'1x1' | keine Zahl | keine Zahl
'-.' | keine Zahl | Zahl
'1...0' | keine Zahl | Zahl
'08sjfg' | keine Zahl | keine Zahl
'15:30h' | keine Zahl | keine Zahl

Aber ... keine Ahnung, welche Varianten ich auch noch vergessen habe zu prüfen.

Meine RegExp lässt zB keine eigentlichen Zahlen mit Tausenderpunkten durch, weill ... weil ich die nicht durchlasse, um einen Fehler bei TO_NUMBER('1.000,00') zu vermeiden.

---
Unterm Strich:
Alles zu wackelig...
Evtl kannst du dir eine PL/SQL-Function "ISNUMERIC()" zusammenbraten, die verlässlicer ist... da sollte es ein paar Varianten im Netz geben.
Aber das war ja nicht die Frage.

Grüße
Biber