Utiliser max dans un VARCHAR pour obtenir un résultat > 999999 ?

Question

Que faire si quelqu'un a créé une colonne en tant que VARCHAR2(256 CHAR) et qu'il n'y a que des chiffres dans cette colonne. Je voudrais obtenir le nombre le plus élevé. Le problème est le suivant : le nombre est supérieur à 999999 mais un Max vers un varchar me donne toujours un nombre maximal de 999999.

J'ai essayé to_number(max(numbers), '9999999999999') mais je reçois toujours 999999 en retour, ce n'est pas possible. Avez-vous une idée ? Je vous remercie.

Answer 1

Le meilleur moyen est de

Première solution convertir la colonne en numérique

o

Deuxième solution convertir les données de votre requête en numérique et ensuite obtenir les données...

Exemple

 select max(col1) from(
  select to_number(numbers) as col1 from table ) d

Il doit en être ainsi parce que si vous appelez MAX() avant TO_NUMBER(), le tri se fera par ordre alphabétique, et alors 999999 sera plus grand que 100000000000. Notez que l'application de TO_NUMBER() à une colonne de type varchar2 entraîne le risque d'une exception INVALID_NUMBER, si la colonne contient des caractères non numériques. C'est pourquoi la première solution proposée est à privilégier.

Answer 2

Dans Oracle, le type NUMBER contient des valeurs à virgule flottante en base 100 qui ont une précision de 38 chiffres significatifs, et une valeur maximale de 9999...(38 9) x 10^125. Deux questions se posent : la première est de savoir si un NUMBER peut contenir une valeur convertie à partir d'une chaîne de 256 caractères, et la seconde est de savoir si deux valeurs de ce type qui sont "proches" en termes numériques peuvent être distinguées.

Commençons par prendre une chaîne de 256 caractères et essayons de la convertir en un nombre. La chose la plus évidente à faire est :

SELECT TO_NUMBER('9999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999') AS VAL
  FROM DUAL;

En exécutant ce qui précède, on obtient :

ORA-01426: numeric overflow

ce à quoi nous nous attendions, ayant prêté attention plus tôt. Le plus grand exposant qu'un NUMBER peut gérer est 125 - et ici nous essayons de convertir une valeur avec 256 chiffres significatifs. Les NUMEROS ne peuvent pas gérer cela. Si nous réduisons le nombre de chiffres à 125, comme suit :

SELECT TO_NUMBER('99999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999999') AS VAL
  FROM DUAL;

Cela fonctionne bien, et notre réponse est 1E125.

< clignotement >

WHOA ! ATTENDEZ ! QUOI ? ?? La réponse est 1 x 10^125 ??? Qu'en est-il de tous ces 9 ?!?!?!?

Vous vous souvenez que j'ai mentionné plus tôt qu'un NUMÉRO Oracle est un valeur en virgule flottante avec une précision maximale de 38 et un exposant maximal de 125. Du point de vue de TO_NUMBER, il est impossible de représenter exactement 125 9, car il y a trop de chiffres (n'oubliez pas que la précision maximale est de 38 (nous y reviendrons plus tard)). Il fait donc du mieux qu'il peut - il convertit les 38 premiers chiffres (qui sont tous des 9) et se demande ensuite "comment arrondir au mieux pour que le résultat soit A) représentatif de l'entrée et B) aussi proche que possible de ce qui m'a été donné". Dans ce cas, il regarde le chiffre 39, voit qu'il s'agit d'un 9 et décide d'arrondir au chiffre supérieur. Comme tous les autres chiffres sont également des 9, il continue à arrondir proprement jusqu'à ce qu'il se retrouve avec 1 comme chiffre restant de la mantisse.

* Plus tard, de retour au ranch... *

OK, plus tôt, j'ai mentionné que NUMÉRO a une précision de 38 chiffres. Ce n'est pas entièrement C'est vrai - il peut en fait faire la différence entre des valeurs ayant jusqu'à 40 chiffres de précision, du moins parfois, si le vent est bon et que vous descendez une pente. Voici un exemple :

SELECT CASE
         WHEN to_number('9999999999999999999999999999999999999999') >
              to_number('9999999999999999999999999999999999999998')
           THEN 'Greater'
           ELSE 'Not greater'
       END AS VAL
  FROM DUAL;

Ces deux valeurs ont chacune 40 chiffres (le comptage est laissé comme un exercice au lecteur qui s'ennuie beaucoup :-). Si vous exécutez la commande ci-dessus, vous obtiendrez les résultats suivants "Plus grand montrant que la comparaison de deux valeurs de 40 chiffres a réussi.

Maintenant, pour s'amuser. Si vous ajoutez un "9" supplémentaire à chaque chaîne, ce qui donne une valeur de 41 chiffres, et que vous réexécutez l'instruction, elle renverra "Pas plus grand".

< clignotement >

ATTENDEZ ! QUOI ?? WHOA !!! Ces valeurs sont évidemment différent ! Même un TotalFool (tm) peut le voir ! !!

Le problème ici est qu'un nombre de 41 chiffres dépasse la précision du type NUMBER, et donc quand TO_NUMBER trouve qu'il a une valeur aussi longue, il commence à éliminer les chiffres du côté droit. Ainsi, même si ces deux très grands nombres sont clairement différents de vous et moi, ils ne le sont plus du tout une fois qu'ils ont été pliés, filés, mutilés et convertis.

Alors, quels sont les points à retenir ?

1 - Pour répondre à la question initiale de l'OP, vous devez trouver un autre moyen de comparer vos chaînes de chiffres que d'utiliser NUMBER, car le type NUMBER d'Oracle ne peut pas contenir des valeurs de 256 chiffres. Je vous suggère de normaliser les chaînes en vous assurant que TOUTES les valeurs comportent 256 chiffres, en ajoutant des zéros à gauche si nécessaire, puis une comparaison de chaînes devrait fonctionner correctement.

2 - Les nombres à virgule flottante prouvent l'existence de (votre/vos divinité(s) préférée(s) ici) par négation, car ils sont clairement l'œuvre de (votre personnification du mal préférée ici). Chaque fois que vous travaillez avec eux (comme nous le faisons tous, tôt ou tard), vous devez vous rappeler qu'ils sont les sous-produits immondes d'un mal mal malin, attendant de se déchaîner sur vous quand vous vous y attendez le moins.

3 - Il y a NON Point 3 ! (Et un crédit supplémentaire pour ceux qui peuvent identifier sans avoir recours à un moteur de recherche extra-crânien. d'où cela vient :-)

Partagez et appréciez.

Answer 3

Si vous voulez dire que les chiffres de la colonne peuvent être que grand (256 chiffres), vous pourriez essayer quelque chose comme ceci :

SELECT numbers
FROM (
  SELECT numbers
  FROM table_name
  ORDER BY LPAD(numbers, 256) DESC
)
WHERE rownum = 1

ou comme ça :

SELECT LTRIM(MAX(LPAD(numbers, 256))) AS numbers
FROM table_name