vérifier si la fonction" c'est un nombre " dans Oracle

Une autre idée, mentionnée ici est d'utiliser une expression régulière pour vérifier:

SELECT  foo 
FROM    bar
WHERE   REGEXP_LIKE (foo,'^[[:digit:]]+$');

la partie agréable est que vous n'avez pas besoin d'une fonction PL/SQL séparée. Potentiellement problématiques partie est qu'une expression régulière peut ne pas être la méthode la plus efficace pour un grand nombre de lignes.

réponse de Saish en utilisant REGEXP_LIKE est la bonne idée, mais ne supporte pas les nombres flottants. Celui-ci sera ...

valeurs de retour numériques

SELECT  foo 
FROM    bar
WHERE   REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');

valeurs de retour non numériques

SELECT  foo 
FROM    bar
WHERE   NOT REGEXP_LIKE (foo,'^-?\d+(\.\d+)?$');

vous pouvez tester vos expressions régulières eux-mêmes jusqu'à ce que votre cœur se contente de http://regexpal.com / (mais assurez-vous de cocher la case lors des sauts de ligne pour celui-ci).

vous pouvez utiliser la fonction d'expression régulière 'regexp_like' dans ORACLE (10g)comme suit:

select case
       when regexp_like(myTable.id, '[[:digit:]]') then
        case
       when myTable.id > 0 then
        'Is a number greater than 0'
       else
        'Is a number less than or equal to 0'
     end else 'it is not a number' end as valuetype
from table myTable

il s'agit d'un duplicata potentiel de lignes de recherche qui ne contiennent pas de données numériques dans Oracle . Voir aussi: Comment puis-je déterminer si une chaîne est numérique en SQL? .

Voici une solution basée sur de Michael Durrant qui fonctionne pour les entiers.

SELECT foo
FROM bar
WHERE DECODE(TRIM(TRANSLATE(your_number,'0123456789',' ')), NULL, 'number','contains char') = 'number'

Adrian Carneiro a publié une solution qui fonctionne pour les décimales et autres. Cependant, comme Justin Cave a souligné, cela classera incorrectement les chaînes comme "123.45.23.234" ou "131+234".

SELECT foo
FROM bar
WHERE DECODE(TRIM(TRANSLATE(your_number,'+-.0123456789',' ')), NULL, 'number','contains char') = 'number'

si vous avez besoin d'une solution sans PL/SQL ou REGEXP_LIKE, cela peut vous aider.

je suis contre l'utilisation de when others donc j'utiliserais (retourner un" entier booléen "en raison de SQL ne supporte pas les booléens)

create or replace function is_number(param in varchar2) return integer
 is
   ret number;
 begin
    ret := to_number(param);
    return 1; --true
 exception
    when invalid_number then return 0;
 end;

dans L'appel SQL vous utiliseriez quelque chose comme

select case when ( is_number(myTable.id)=1 and (myTable.id >'0') ) 
            then 'Is a number greater than 0' 
            else 'it is not a number or is not greater than 0' 
       end as valuetype  
  from table myTable

comment la colonne est-elle définie? Si c'est un champ varchar, alors ce n'est pas un nombre (ou stocké comme un). Oracle peut être en mesure de faire la conversion pour vous (par exemple, select * de someTable où charField = 0), mais il ne retournera les lignes où la conversion est vraie et est possible. C'est aussi loin de la situation idéale de performance sage.

donc, si vous voulez faire des comparaisons de nombres et traiter cette colonne comme un nombre, peut-être qu'il devrait être défini comme un nombre?

cela dit, voici ce que vous pourriez faire:

create or replace function myToNumber(i_val in varchar2) return number is
 v_num number;
begin
 begin
   select to_number(i_val) into v_num from dual;
 exception
   when invalid_number then
   return null;
 end;
 return v_num;
end;

Vous pouvez également inclure les autres paramètres que le to_number. Utiliser comme suit:

select * from someTable where myToNumber(someCharField) > 0;

il ne retournera aucune ligne que Oracle considère comme un nombre invalide.

santé.

CREATE OR REPLACE FUNCTION is_number(N IN VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN CASE regexp_like(N,'^[\+\-]?[0-9]*\.?[0-9]+$') WHEN TRUE THEN 1 ELSE 0 END;
END is_number;

s'il vous plaît noter qu'il ne considérera pas 45e4 comme un nombre, mais vous pouvez toujours changer regex pour accomplir le contraire.

@JustinCave-le remplacement de" when value_error "Pour" when others " est un bon raffinement de votre approche ci-dessus. Cette légère modification supplémentaire, bien que conceptuellement la même, supprime l'exigence pour la définition de et l'allocation de mémoire conséquente à votre variable l_num:

function validNumber(vSomeValue IN varchar2)
     return varchar2 DETERMINISTIC PARALLEL_ENABLE
     is
begin
  return case when abs(vSomeValue) >= 0 then 'T' end;
exception
  when value_error then
    return 'F';
end;

juste une note aussi à quiconque préférant émuler la logique de format de nombre Oracle en utilisant L'approche REGEXP "plus risquée", s'il vous plaît n'oubliez pas de considérer NLS_NUMERIC_CHARACTERS et NLS_TERRITORIUM.

c'est ma requête pour trouver tous ceux qui ne sont pas numéro:

Select myVarcharField
From myTable
where not REGEXP_LIKE(myVarcharField, '^(-)?\d+(\.\d+)?$', '')
and not REGEXP_LIKE(myVarcharField, '^(-)?\d+(\,\d+)?$', '');

dans mon domaine . et, les nombres décimaux ont malheureusement dû tenir compte de cela, sinon vous n'avez besoin que d'une seule de la restriction.

Eh bien, vous pouvez créer la fonction is_number à appeler pour que votre code fonctionne.

create or replace function is_number(param varchar2) return boolean
 as
   ret number;
 begin
    ret := to_number(param);
    return true;
 exception
    when others then return false;
 end;

EDIT: reportez-vous à la réponse de Justin. J'ai oublié ce petit détail pour un appel SQL pur....

fonction pour numéro mobile de longueur 10 chiffres et à partir de 9,8,7 en utilisant regexp

create or replace FUNCTION VALIDATE_MOBILE_NUMBER
(   
   "MOBILE_NUMBER" IN varchar2
)
RETURN varchar2
IS
  v_result varchar2(10);

BEGIN
    CASE
    WHEN length(MOBILE_NUMBER) = 10 
    AND MOBILE_NUMBER IS NOT NULL
    AND REGEXP_LIKE(MOBILE_NUMBER, '^[0-9]+$')
    AND MOBILE_NUMBER Like '9%' OR MOBILE_NUMBER Like '8%' OR MOBILE_NUMBER Like '7%'
    then 
    v_result := 'valid';
    RETURN v_result;
      else 
      v_result := 'invalid';
       RETURN v_result;
       end case;
    END;

je crois que la solution suivante (basée sur l'approche regexp_like ci-dessus) est optimale:

function isInteger(vYourValue IN varchar2)
         return varchar2
         is
begin
  return case REGEXP_INSTR(vYourValue,'^[[:digit:]]+$') when 0 then 'F' else 'T' end;
end;

pourquoi je dis ça?

1. le sous-ensemble numérique testé peut être modifié tel que désiré en changeant le regexp de façon appropriée.

2. il peut être utilisé en SQL IE select isInteger(myCol) à partir de mytable; comme il retourne 'T' de 'F' au lieu de booléen.

3. il peut être utilisé nativement en pl/sql ie. si isInteger (vMyValue) = 'T' alors ....

4. il satisfait une assertion WNDS,wnps purity.

5. il ne s'appuie pas, à mon avis, sur l'approche trop large "when others" pour la détermination des résultats.

Notez que regexp ou en fonction des approches sont plusieurs fois plus lent que la plaine sql condition .

ainsi quelques solutions heuristiques avec une applicabilité limitée font sence pour des scans énormes.

il y a une solution pour les cas où vous savez avec certitude que des valeurs non numériques contiendraient des lettres alphabétiques:

select case when upper(dummy)=lower(dummy) then '~numeric' else '~alpabetic' end from dual

Et si vous savez quelque lettre serait toujours présents dans les cas non numériques:

select case when instr(dummy, 'X')>0 then '~alpabetic' else '~numeric' end from dual

quand les cases numériques contiendraient toujours zéro:

select case when instr(dummy, '0')=0 then '~alpabetic' else '~numeric' end from dual

si la condition est nulle, alors c'est le nombre

IF(rtrim(P_COD_LEGACY, '0123456789') IS NULL) THEN
                return 1;
          ELSE
                return 0;
          END IF;