Problème avec fonction CHAR_LENGTH [Résolu]

[waxx88]

Bonjour,

A partir d'une table contenant des individus, je souhaite pouvoir différencier les personnes dont le noms contiennent des chiffres afin de ne pas les traiter par des requêtes d'uniformisation de la base.

La solution que j'ai trouvée consiste à "effacer" du nom tous les chiffres à l'aide de la fonction LTRIM puis de comparer la longueur de chaine de caractère à celle initiale. Si la chaîne initiale est différente (inférieure) de celle traitée par LTRIM, cela signifie qu'elle contient des chiffres.

Voici mon code :

Code :

1
2
SELECT NOM, char_length(LTRIM(NOM,'0123456789')) AS a ,char_length(NOM) AS b FROM TABLE
WHERE(SEXE='M' AND a > b )

J'ai l'erreur suivante : ORA-00904: "CHAR_LENGTH" : identificateur non valide

[mnitu]

C’est basique non ? Il vous dit qu’il ne connaît pas char_length. En Oracle la fonction est length.
Mais votre algo ne marche pas pour TOTO92 ni pour TO92TO.

[waxx88]

Merci mnitu
Effectivement, la fonction LENGTH est plus appropriée.

En ce qui concerne mon algorithme, effectivement celà ne marchera pas dans les cas données ( TO92TO et TOTO92).

En fait, je recherche un moyen plus simple d'éviter à utiliser les conditions suivantes :

Code :

1
2
3
4
5
6
7
8
9
10
NOM NOT LIKE '%0'
AND NOM NOT LIKE '%1'
ANDNOM NOT LIKE '%2'
AND NOM NOT LIKE '%3'
AND NOM NOT LIKE '%4'
AND NOM NOT LIKE '%5'
AND NOM NOT LIKE '%6'
AND NOM NOT LIKE '%7'
AND NOM NOT LIKE '%8'
AND NOM NOT LIKE '%9'

C'est à dire trouver une fonction qui me permette de détecter la présence d'un chiffre dans une chaine de caractères.
J'ai déjà trouvé et essayé des propositions telles que "[0-9]" mais elles ne marchent pas.

[mnitu]

A partir de la version 10g vous pouvez utiliser des expressions régulières

Code :

1
2
3
4
 
SELECT regexp_instr('TO92TO','[[:digit:]]') 
  FROM dual
/

[McM]

Avec translate, c'est fait pour.

J'ai géré le cas du nom NULL avec les NVL sur les length, ça dépend du besoin.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH t AS (			SELECT 'abc' nom FROM dual
UNION SELECT '' FROM dual
UNION SELECT 'TOTO92' FROM dual
UNION SELECT 'TO92TO' FROM dual
)
SELECT nom, TRANSLATE(nom, 'a0123456789', 'a') tr, LENGTH(TRANSLATE(nom, 'a0123456789', 'a')) len
FROM t
WHERE NVL(LENGTH(TRANSLATE(nom, 'a0123456789', 'a')),0) = NVL(LENGTH(nom), 0)
 
 
NOM	TR	LEN
abc	abc	3
<null> <null> <null>

[waxx88]

Merci à Mnitu (mais je ne peux pas utilisé les regexp) et à MCM pour cette solution qui fonctionne très bien.
J'avais déjà essayé avec la fonction TRANSLATE() sans succès, comment pouvez-vous me traduire en explications le fonctionnement de "TRANSLATE(nom, 'a0123456789', 'a')" je crois que quelque chose m'échappe ...
Cordialement.

[McM]

translate remplace chaque caractère du 2nd param (à remplacer) par le caractère à la même position du 3ème param (remplacement).
translate(chaine, '123', '456') => remplace tous les 1 par 4, tous les 2 par 5 et tous les 3 par 6.
S'il n'y a pas de caractère à la même position de remplacement, on remplace par NULL (on supprime donc le caractère de la chaine).

Le troisième paramètre est obligatoire et non null (sinon translate va renvoyer null), donc tu es obligé de remplacer quelque chose par quelque chose.

Code :

TRANSLATE(nom, 'a0123456789', 'a')

Dans le cas présent, on remplace 'a' par 'a', et les chiffres 0-9 par rien.

L'autre utilisation intéressante de translate est la double utilisation pour supprimer tous les caractères autres que 0-9 par exemple.
Si tu comprends bien cet exemple translate n'aura plus de secret pour toi.

Code :

1
2
3
4
5
6
SELECT TRANSLATE('a123b8iu', 'a0123456789','a') sans_chiffre,
  TRANSLATE('a123b8iu', '0'||TRANSLATE('a123b8iu', 'a0123456789','a'), '0') que_chiffres
FROM dual
 
SANS_CHIFFRE	QUE_CHIFFRES
abiu			1238

[waxx88]

Merci pour ces explications claires et ces exemples parlants.
Effectivement, cette fonction est vraiment intéressante.