“Coffre-fort” TO_NUMBER()

SELECT TO_NUMBER('*') FROM DUAL

De toute évidence, cela me donne une exception:

ORA-01722: nombre non valable

Est-il un moyen de "sauter" et obtenir 0 ou NULL à la place?

L'ensemble de la question: j'ai NVARCHAR2 domaine, qui contient des nombres et presque pas 😉 (comme *) et j'ai besoin de sélectionner le plus grand nombre de la colonne.

Oui, je sais, c'est un terrible du design, mais c'est ce dont j'ai besoin maintenant... :-S

UPD:

Pour moi, je l'ai résolu ce problème avec

COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+')), 0)

Est * le seul personnage que vous vous attendez à rencontrer?
Poneys: nan... j'ai résolu ce problème avec laide REGEXP_SUBSTR(field, '^\d+')

InformationsquelleAutor zerkms | 2010-12-20

15

Je ne pouvais pas trouver quelque chose de mieux que cela:
```
function safe_to_number(p varchar2) return number is
    v number;
  begin
    v := to_number(p);
    return v;
  exception when others then return 0;
end;
```
- Yep, j'ai aussi décidé d'écrire mon propre fonction, un peu différente de la vôtre (parce que 54-3 devrait devenir 54, je n'ai pas mentionné que, désolé).
- Vous feriez mieux de les ignorer uniquement ORA-01722 ici.
- Prendre en compte le fait que personnalisé pl/sql fonctions importantes de performance généraux qui peuvent ne pas être adaptés pour les poids lourds requêtes.
- Note de la réponse ci-dessous montrant la façon de gérer ce natif de 12c r2 à partir de stackoverflow.com/a/45886745/587365
InformationsquelleAutor Gabe
25
```
COALESCE(TO_NUMBER(REGEXP_SUBSTR(field, '^\d+(\.\d+)?')), 0) 
```
permettra également d'obtenir des numéros à l'échelle > 0 (chiffres à droite du point décimal).
- C'est une bonne solution, mais l'expression régulière doit être ajustée pour chaque scénario spécifique. Cette expression pourrait fonctionner mieux: ^\-?\d*\.?\d*$ qui permet de nombres négatifs et les numéros commençant par ., et n'autorise pas non numériques caractères de fin. Le ^\d+(\.\d+)? regex rendements 1 si field contient 1x, et 0' if champ " contient 0.0.1, et 0 si field contient 0 6, etc.
InformationsquelleAutor pweitzman
13

De Oracle Database 12c Release 2 vous pouvez utiliser TO_NUMBER avec DEFAULT ... ON CONVERSION ERROR:
```
SELECT TO_NUMBER('*' DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;
```
Ou CAST:
```
SELECT CAST('*' AS NUMBER DEFAULT 0 ON CONVERSION ERROR) AS "Value"
FROM DUAL;
```
db<>le violon de démonstration

InformationsquelleAutor Lukasz Szozda

select COALESCE(TO_NUMBER(REGEXP_SUBSTR( field, '^(-|+)?\d+(\.|,)?(\d+)?$')), 0) from dual;

Il vous permet de convertir 123 à 123, mais 123a ou 12a3 à 0.

InformationsquelleAutor sOliver

7

Côté la question originale et plutôt old skool
```
select a, decode(trim(translate(b,'0123456789.',' ')),null,to_number(b),0)  from 
(
    select '1' a, 'not a number' b from dual
    union
    select '2' a, '1234' b from dual
)
```
- Laide en effet, mais très puissant!
- les objets internes sont souvent, mais comme vous le dites, ils sont performants. Des travaux autour Oracle7 (de mémoire)
- cela va faire 543 de la chaîne 54-3. Je n'en fait pas mention dans la question, mais dans ce cas j'attends 54 (et c'est ce que ma solution avec regexp n').
- pensé qu'il serait de produire des 0 de 54-3, de toute façon, comme je l'ai dit, était de répondre à la question initiale avant les commentaires. Comme votre solution finale si.
- Échoue si le numéro contient des espaces: sélectionnez un, decode(trim(translate(b,'0123456789.',' ')),null,to_number(b),0) from ( select '1' a, '12 34' b de double )
- Ne pouvait pas utiliser les regex dans mon application, de sorte que cela a très bien fonctionné
InformationsquelleAutor stjohnroe

C'est probablement un peu brouillon rouler votre propre regexp pour tester un certain nombre, mais le code ci-dessous pourrait fonctionner. Je pense que l'autre solution par Gabe impliquant une fonction définie par l'utilisateur est plus robuste puisque vous êtes en utilisant l'Oracle de la fonctionnalité (et ma regexp n'est probablement pas correct à 100%), mais il pourrait être la peine d'aller:

with my_sample_data as (
  select '12345' as mynum from dual union all
  select '54-3' as mynum from dual union all
  select '123.4567' as mynum from dual union all
  select '.34567' as mynum from dual union all
  select '-0.3462' as mynum from dual union all
  select '0.34.62' as mynum from dual union all
  select '1243.64' as mynum from dual 
)
select 
  mynum, 
  case when regexp_like(mynum, '^-?\d+(\.\d+)?$') 
    then to_number(mynum) end as is_num
from my_sample_data

Cela donnera alors la sortie suivante:

MYNUM   IS_NUM
-------- ----------
12345   12345
54-3    
123.4567    123.4567
.34567  
-0.3462 -0.3462
0.34.62 
1243.64 1243.64

InformationsquelleAutor Mike Meyers

select DECODE(trim(TRANSLATE(replace(replace(A, ' '), ',', '.'), '0123456789.-', ' ')),
              null,
              DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '.', INSTR(replace(replace(A, ' '), ',', '.'), '.') + 1),
                     0,
                     DECODE(INSTR(replace(replace(A, ' '), ',', '.'), '-', 2),
                            0,
                            TO_NUMBER(replace(replace(A, ' '), ',', '.'))))) A
  from (select '-1.1' A from DUAL union all select '-1-1' A from DUAL union all select ',1' A from DUAL union all select '1..1' A from DUAL) A;

Présent code, à l'exclusion des chaînes comme: -1-1, 1..1, 12-2 et ainsi de suite. Et je n'ai pas utilisé les expressions régulières ici.

InformationsquelleAutor usbo

Une combinaison de solutions précédentes (à partir de @sOliver et @Mike Meyers) et en essayant d'attraper autant de numéros que possible par la suppression de la dernière '$' à partir de REGEXP.

Il peut être utilisé pour filtrer le nombre réel à partir d'une table de configuration, et ont une "sorte-de" commentaire suivant le nombre que "12 Jours".

with my_sample_data as ( select '12345' as mynum from dual union all select '123.4567' as mynum from dual union all select '-0.3462' as mynum from dual union all select '.34567' as mynum from dual union all select '-.1234' as mynum from dual union all select '**' as mynum from dual union all select '0.34.62' as mynum from dual union all select '24Days' as mynum from dual union all select '42ab' as mynum from dual union all select '54-3' as mynum from dual ) SELECT mynum, COALESCE( TO_NUMBER( REGEXP_SUBSTR( mynum, '^(-|+)?\d*(.|,)?(\d+)?') ) , 0) is_num FROM my_sample_data;

donnerait


MYNUM    IS_NUM                                  
-------- ----------
12345    12345                                   
123.4567 123.4567                                
-0.3462  -0.3462                                 
.34567   0.34567                                 
-.1234   -0.1234                                 
**       0                                       
0.34.62  0.34                                    
24Days   24                                      
42ab     42                                      
54-3     54

InformationsquelleAutor Victor H

0

Meilleure méthode semble être la fonction la solution, mais si vous n'avez pas les privilèges nécessaires dans l'environnement, vous êtes en difficulté (comme moi), alors vous pouvez essayer celui-ci:
```
SELECT
 CASE
  WHEN
     INSTR(TRANSLATE('123O0',
                     ' qwertyuıopğüasdfghjklşizxcvbnmöçQWERTYUIOPĞÜASDFGHJKLŞİZXCVBNMÖÇ~*\/(){}&%^#$<>;@€|:_=',
                     'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
                     ),
       'X') > 0
  THEN 'Y'
  ELSE 'N'
END is_nonnumeric
FROM DUAL
```
Par la route: Dans mon cas, le problème était dû à des "," et "." 🙂 Pour prendre cela en considération. Inspiré de cette une. Aussi cette une semble plus concis.

Par la voie 2: Cher Oracle, pouvez-vous s'il vous plaît créer quelques fonctions intégrées pour de petits mais précieux besoins?

InformationsquelleAutor Gultekin

Vous devez vous connecter pour publier un commentaire.