Principes fondamentaux des fonctions d'extraction de chaînes

Oracle SQL propose une famille de fonctions permettant d'extraire une portion d'une chaîne source. La fonction SUBSTR prend trois paramètres principaux : la chaîne d'origine, la position de départ et la longueur souhaitée. Selon la vairante utilisée, la méthode de calcul de la position et de la longueur diffère considérablement :

• SUBSTR : compte en nombre de caractères
• SUBSTRB : compte en nombre d'octets
• SUBSTRC : utilise les caractères Unicode complets
• SUBSTR2 : utilise les points de code UCS2
• SUBSTR4 : utilise les points de code UCS4

Règles applicables aux paramètres

• Une position de valeur 0 est implicitement traitée comme 1.
• Une position positive entraîne un parcours de gauche à droite depuis le premier caractère.
• Une position négative provoque un repérage de droite à gauche, le dernier caractère servant de point de repère initial.
• Si la longueur n'est pas renseignée, l'extraction s'étend jusqu'à la fin de la chaîne à partir de la poistion indiquée.
• Une longueur négative provoque un retour de valeur NULL.

Le paramètre source accepte les types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB et NCLOB. Toutefois, seules les fonctions SUBSTR et SUBSTRB tolèrent les types CLOB et NCLOB. Les paramètres de position et de longueur doivent être de type NUMBER ou convertibles implicitement en nombre entier. Lorsque le paramètre source est de type CHAR ou NCHAR, le type retenu en retour est respectivement VARCHAR2 et NVARCHAR2. Les valeurs décimales sont tronquées en entiers automatiquement.

Exemples pratiques

Exemple 1 : extraction avec position positive

SELECT SUBSTR('ABCDEFG', 3, 4) AS extrait FROM DUAL;

Résultat : CDEF. L'extraction débute au troisième caractère et capture quatre caractères vers la droite.

Exemple 2 : extraction avec position négative

SELECT SUBSTR('ABCDEFG', -5, 4) AS extrait FROM DUAL;

Résultat : CDEF. Le point de départ est localisé en partant de la fin, mais le sens d'extraction reste toujours de gauche à droite. Ce comportement diffère de la fonction INSTR, où une position négative inverse également le sens de recherche des occurrences.

Exemple 3 : SUBSTRB et l'impact de l'encodage

SELECT SUBSTRB('ABCDEFG', 5, 4.2) AS extrait_octets FROM DUAL;

Contrairement à SUBSTR qui ne compte que des caractères, SUBSTRB compte en octets. Le résultat dépend donc directement du jeu de caractères configuré dans la base de données. Avec un encodage GBK, chaque lettre occupant un octet, le résultat sera EFG (du cinquième octet jusqu'à la fin, la chaîne ne contenant que 7 octets). Avec un encodage Unicode où chaque caractère occupe deux octets, le résultat serait CD.

Aperçu des encodages courants et taille des caractères

Mode de stockage des chaînes VARCHAR2

Le type VARCHAR2 peut être déclaré de deux manières : VARCHAR2(n CHAR) ou VARCHAR2(n BYTE). Lorsqu'on écrit simplement VARCHAR2(n), Oracle se réfère au paramètre de configuration NLS_LENGTH_SEMANTICS pour déterminer l'unité de mesure. Pour vérifier ce paramètre :

SHOW PARAMETER nls_length_semantics;

Vérification du jeu de caractères de la base

La requête suivante permet d'identifier le jeu de caractères utilisé par la base connectée :

SELECT userenv('language') FROM dual;

L'encodage et le mode de stockage déterminent conjointement la capacité d'un champ VARCHAR2. Par exemple, un champ VARCHAR2(4) en mode BYTE avec un encodage GBK peut contenir au maximum 4 lettres latines ou 2 sinogrammes. Avec un encodage UTF-8, ce même champ ne pourrait stocker qu'un seul sinogramme de 3 octets, laissant un octet insuffisant pour un second caractère multi-octets.