2
votes

Comment utiliser entre la condition avec des nombres pour les colonnes de chaîne dans Oracle

J'ai besoin de vérifier la condition entre pour deux nombres dans les tables mais la colonne dans oracle DB a des valeurs de chaîne.

J'ai essayé la requête suivante, rien ne m'a aidé

select * from  sys.employee_infor where  to_number(emp_number) between 1200 and 2400;

select * from  sys.employee_infor where (emp_number) >= to_char(1200) and (emp_number)  <= to_char(2400);

select * from  sys.employee_infor where  to_number(emp_number) between '1200' and '2400';

Erreur reçue en tant que:

ORA-01722: numéro invalide

Ma colonne Emp_number ressemble à,

sql oracle oracle11g oracle10g

4 commentaires

Pouvez-vous fournir des exemples de données? pourquoi allez-vous utiliser la valeur du numéro de magasin de type chaîne dans la colonne emp_number ?

N'utilisez pas de schéma SYS pour vos propres données. SYS (et SYSTEM) sont spéciaux, laissez-les tranquilles. Si vous faites quelque chose inattendu , vous risquez de détruire la base de données. Créez un autre utilisateur et faites tout ce que vous faites là-bas.

JAMAIS , jamais créer vos propres tables dans le schéma SYS ou SYSTEM . Créez un utilisateur régulier et créez vos tables là-bas. N'utilisez PAS SYS ou SYSTEM pour votre application ou autre chose que de faire un travail DBA. Ne le fais pas

Ma colonne sera comme ça

3 Réponses :

1
votes

Il est probable que vous ayez des données dans la colonne du numéro d'employé qui ne peuvent pas être converties en nombre.

En supposant que le numéro d'employé est une valeur entière, la requête suivante vous montrera les lignes incriminées:

select * 
from sys.employee_infor 
where to_number(emp_number default 0 on conversion error) between 1200 and 2400

Vous pouvez utiliser cette clause where dans votre requête pour ignorer les données mal formatées:

select * from (
    select * from sys.employee_infor where not regexp_like(emp_number, '[^0-9]')
) where  to_number(emp_number) between 1200 and 2400

PS: car vous cherchez à comparer des nombres , pas des chaînes.

Une autre solution consiste à utiliser l'option DEFAULT ... ON CONVERSION ERROR de la fonction TO_NUMBER () , disponible à partir d'Oracle 12c R2. Avec cette option, les erreurs de conversion sont piégées et une valeur par défaut est renvoyée au lieu de renvoyer une erreur:

select * from sys.employee_infor where regexp_like(emp_number, '[^0-9]');

0 commentaires

2
votes

La solution la plus sûre consiste à utiliser CASE :

SELECT *
FROM sys.employee_infor
WHERE CASE WHEN NOT REGEXP_LIKE(emp_number, '\D') THEN TO_NUMBER(emp_number) END BETWEEN 1200 AND 2400

2 commentaires

REGEXP_LIKE prend beaucoup de temps pour exécuter la requête, je dois inclure cette sous-requête dans la requête principale et Sys n'est qu'un exemple dont le nom d'utilisateur est différent de sys.Veuillez m'aider à résoudre le problème

La bonne solution est de stocker les nombres sous forme de nombres. Le repos n'est que des solutions de contournement. Vous pouvez essayer d'utiliser une regex plus efficace, par exemple CASE WHEN REGEXP_LIKE (emp_number, '^ \ d {1,10} $') THEN TO_NUMBER ... ou ajoutez un autre filtre, par exemple WHERE emp_number ENTRE '0' ET '9' ET CASE ... . Ou utilisez la solution en cas d'erreur de conversion dans l'autre réponse.

0
votes

Dans de nombreuses circonstances, cela fonctionnera:

where emp_number >= '1200' and emp_number <= '2400' and
      length(emp_number) = 4

ou:

where emp_number >= '1200' and emp_number <= '2400'

Ce n'est pas exactement la même chose, car il utilise des comparaisons de chaînes et non des comparaisons numériques. D'autre part, il peut tirer parti des index.

1 commentaires

Merci pour la suggestion, mais lorsque j'utilise la condition unique, elle renvoie la valeur, mais en utilisant les deux conditions, rien n'a été retourné. Et si la comparaison entre «100» et «1000»