Select sur VARCHAR2

Version imprimable

Select sur VARCHAR2

Avant de commencer, je ne suis pas le créateur de la structure de la table, simplement un utilisateur ... donc on ne rigole pas à la vue de la structure ...

Voici la structure de la table :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
C1          NOT NULL VARCHAR2(32)
C2                             NOT NULL VARCHAR2(16)
C3                             NOT NULL VARCHAR2(16)
C4                             NOT NULL VARCHAR2(16)
C5                             NOT NULL NUMBER
C6                             DATE
C7                             VARCHAR2(16)
C8                             VARCHAR2(16)
C9                             VARCHAR2(8)
C10                           VARCHAR2(32)
C11                           NOT NULL VARCHAR2(8)
C12                           VARCHAR2(16)
C13                           VARCHAR2(20)
C14                           VARCHAR2(10)
...
etc

La clé primaire est réalisée par C1,C2,C3,C4,C5.

Je tape cette requete :

Code:

select * from TABLE where C1 like 'ACACH%';

elle ramène 3495 lignes

Maintenant je tapes :

Code:

select * from TABLE where C1 like 'ACACHA%';

elle ramene 0 lignes.

Pourtant dans le résultat précédent on voit :
ACACHA-'_AAAER XXX XXXXXX XXXXXX ...

Alors pourquoi l'ajout du A dans le critère provoque le rejet de toute les lignes ?

17/07/2007, 15h54
McM
Fait un
Code:

1 2 SELECT c1, NLSSORT(c1) from TABLE where C1 like 'ACACH%';
et montre le résultat pour ton ACACHA-...
17/07/2007, 17h15
sinquem

Ouha !! géniale cette commande.
ça donne la séquence HEXA en BASE ?

Code:

ACACHA-'_AAAER 141E141E3714141414286400010101010105002D0027005F050505030100
17/07/2007, 17h40
McM

Normalement oui, c'est ce sur quoi se base oracle pour les tris.
Je ne sais pas suivant les NLS_SORT ce que ça change.
Attention, il rajoute toujours le code '00' à la fin

Mais bon, dans ton cas, je comprend pas le résultat.
17/07/2007, 17h47
sinquem

Si cela peut aider voilà un autre exemple :

Code:

ACACH9952008LU 141E141E37F9F9F5F2F0F0F84B7300010101010101010101010101010100

et un autre mais sur une séquence différente :

Code:

BS0729UTKRT 1969F0F7F2F9736E46646E00010101010101010101010100

Essaye plutôt avec cette fonction

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION f_hexa(p_chaine IN VARCHAR2)
IS
	v_hexa VARCHAR2(100);
	v_chaine VARCHAR2(100) := p_chaine;
BEGIN
	FOR i IN 1..LENGTH(v_chaine)
	LOOP
		v_hexa := v_hexa || ' ' || ASCII(v_chaine);
		v_chaine := SUBSTR(v_chaine, 2);
	END LOOP;
	RETURN(v_hexa);
END;

D'après moi y'a du chr(0) qui s'est incrusté.

17/07/2007, 17h51
sinquem

scuse mais je ne suis pas très coutumier des fonctions PL.
Comment j'execute cela ? et je ne suis d'utilisateur de la base non admin, le create fonction passera-t-il ?
17/07/2007, 17h53
McM

normalement si tu as les droits de création ça marche.
Vérifie avant que la fonction f_hexa n'existe pas (ou vire le OR REPLACE )

ensuite tu fais ton

Code:

select c1, f_hexa(c1) FROM ...
17/07/2007, 17h55
sinquem

Avertissement : Fonction creee avec erreurs de compilation.

Elle ne devait donc pas existée puisque j'ai viré OR REPLACE

Oops : j'avais oublié le retour (faut que tu rajoute le or replace maintenant)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE FUNCTION f_hexa(p_chaine IN VARCHAR2)
RETURN VARCHAR2
IS
	v_hexa VARCHAR2(100);
	v_chaine VARCHAR2(100) := p_chaine;
BEGIN
	FOR i IN 1..LENGTH(v_chaine)
	LOOP
		v_hexa := v_hexa || ' ' || ASCII(v_chaine);
		v_chaine := SUBSTR(v_chaine, 2);
	END LOOP;
	RETURN(v_hexa);
END;

17/07/2007, 17h59
McM

Quand tu auras fini, tu feras un

Code:

DROP FUNCTION F_HEXA
17/07/2007, 18h00
sinquem

message trouvé grace SQLdeveloppeur
Encountered symbol IS when expecting on the following : return

A j'y connais rien mais manquerait pas une paraenthèse ?
17/07/2007, 18h01
sinquem

ça compile !:king:
17/07/2007, 18h02
McM

Yeess :mouarf:
17/07/2007, 18h09
sinquem

Merci grace à ta fonction on le voit mais aussi grace à SQLDevlopper !!

la version DOS m'affichait A, la version ODBC pareil ...

mais en faite il y a de stocker : ACACHÀ-'_ÀÀÀÉR

des lettres Accentuées !!

Mais comment vais-je pouvoir supprimer tout ça ?

C'est encore un coup de NLS_LANG ça ... 8O

Résultat de ta fonction :

Code:

ACACHÀ-'_ÀÀÀÉR 65 67 65 67 72 192 45 39 95 192 192 192 201 82
17/07/2007, 18h20
McM

Ben je sais pas d'où elle viennent tes données.
Si tu veux remplacer les à par des a, une petite recherche avec translate et accents te donnera la solution.
Après faut être sur, parce que tu as des _ ' -

Pour info, le NLSSORT n'est pas toujours le code ascii. Voici le doc de Metalink
https://metalink.oracle.com/metalink...&p_id=227335.1
17/07/2007, 18h28
sinquem

Oui c'est mon gros soucie ...

Ces codes doivent apparaitre certainement à cause du programme qui fait l'insertion en base. Il demande une saisie opérateur et ensuite fait une conversion en majuscule c'est je pense cette partie qui doit/devait être bugguée .
Ces données passent du coup au travers de tous les traitement de purges.

Mais je crois qu'il faut que je trouve une solution pour supprimer tout ce qui est différent de A..Z et 0..9. Il y a une fonction select qui fait cela ou faut-il que je me lance dans du procédural ?

Aprés cela je crois que je pourrais clore le sujet.

D'avance MERCI ... MERCI et encore MERCI :king:
17/07/2007, 18h33
sinquem

Citation:

Envoyé par McM

Ben je sais pas d'où elle viennent tes données.
Si tu veux remplacer les à par des a, une petite recherche avec translate et accents te donnera la solution.
Après faut être sur, parce que tu as des _ ' -

Pour info, le NLSSORT n'est pas toujours le code ascii. Voici le doc de Metalink
https://metalink.oracle.com/metalink...&p_id=227335.1

Je n'ai pas accès à metalink il faut un numéro de support et ça seul le service infra le possède je le crains ...
17/07/2007, 18h40
McM
Pour ce qui est différent de [A-Z][0-9]
voici le code (un peu barbare vu comme ça mais faut le décomposer pour le comprendre)
Code:

1 2 3 4 SELECT TRANSLATE('aAs--26458"2134', '0' || TRANSLATE('as--26458"2134', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0') , '0') FROM dual
Attention ça prend pas les minuscules (suffit de faire un upper) ni les accents.. faut les remplacer aussi comme ça.
17/07/2007, 18h51
sinquem

euhh je le met où mon C1 ? :mrgreen:

PS j'ai cru trouvé une solution sur un autre topic avec une manip :
ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

mais ça marche pas non plus

Code:

1
2
3
4
5
6
SELECT *
FROM TATABLE
WHERE TRANSLATE(TRANSLATE(c1, 'ÀÂÉÈÊÛÙ','AAEEEUU'),
                '0' || TRANSLATE(TRANSLATE(c1, 'ÀÂÉÈÊÛÙ','AAEEEUU'), 
                                '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0')
                , '0') LIKE 'ACAH%'

18/07/2007, 08h52
sinquem

Merci pour le code Translate, cela me sort effectivement toutes les lignes.

Hors, ce que je souhaite c'est pouvoir sélectionner uniquement les lignes qui ne respect pas [A..Z][0..9].

J'y travaille si je trouve je viendrais clore le sujet.
18/07/2007, 09h26
McM

Facile :

Code:

LTRIM(c1, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0132456789') IS NOT NULL
18/07/2007, 10h09
sinquem

:king: PERFECT à une virgule prêt.

c'est is not null :mrgreen:
18/07/2007, 10h23
McM

Ouais, désolé, je corrige.
18/07/2007, 13h41
McM

Je préfère répondre ici que par PM

Citation:

Envoyé par sinquem

Je connaissais pas cette instruction ...

mais quelle est la différence avec rtrim ?
mis à par que l'un commence par la droite et l'autre par la gauche ?

Est-ce que :
LTRIM('...ABC///';'/.') = RTRIM('...ABC///';'/.') ?

RTRIM enlève tous les caractères (2ème chaine de caractère) qui existent à droite (Right) de ta chaine tant qu'un autre caractère n'arrive pas

LTRIM enlève tous les caractères (2ème chaine de caractère) qui existent à gauche (Left) de ta chaine tant qu'un autre caractère n'arrive pas

L'ordre de la seconde chaine (les caractères à effacer) n'a pas d'importance.
LTRIM('...ABC///', '/.') = LTRIM('...ABC///', './') = 'ABC///'
RTRIM('...ABC///', '/.') = RTRIM('...ABC///', './') = '...ABC'