devstory

Des fonctions de traitements de chaines (String) dans Oracle

  1. ASCII
  2. COALESCE
  3. UPPER
  4. LOWER
  5. INITCAP
  6. CHR
  7. CONCAT(Varchar2, Varchar2)
  8. CONCAT(Clob, Clob)
  9. INSTR
  10. INSTRB
  11. LENGTH
  12. LENGTHB
  13. LPAD
  14. LTRIM
  15. RTRIM
  16. TRIM
  17. SUBSTR
  18. SUBSTRB
Site d'apprentissage des langues gratuit:
Suivez-nous sur notre fanpage pour recevoir des notifications chaque fois qu'il y a de nouveaux articles. Facebook

1- ASCII

La fonction ASCII() qui est une fonction d'Oracle PL/SQL, retourne la valeur numérique du premier caractère de la chaîne.
Syntaxe: 

ASCII( p_character )
Paramètre:
p_character
  • Le caractère spécifique tire le code de son NUMBER . La fonction ASCII va donner la valeur du premier caractère de la chaîne en ignorant le reste des caractères.
Observez également la table ASCII suivant:
Dec Hex Oct Char Description   Dec Hex Oct Char Description
0 0 0   null 64 40 100 @  
1 1 1   start of heading 65 41 101 A  
2 2 2   start of text 66 42 102 B  
3 3 3   end of text 67 43 103 C  
4 4 4   end of transmission 68 44 104 D  
5 5 5   enquiry 69 45 105 E  
6 6 6   acknowledge 70 46 106 F  
7 7 7   bell 71 47 107 G  
8 8 10   backspace 72 48 110 H  
9 9 11   horizontal tab 73 49 111 I  
10 A 12   new line 74 4A 112 J  
11 B 13   vertical tab 75 4B 113 K  
12 C 14   new page 76 4C 114 L  
13 D 15   carriage return 77 4D 115 M  
14 E 16   shift out 78 4E 116 N  
15 F 17   shift in 79 4F 117 O  
16 10 20   data link escape 80 50 120 P  
17 11 21   device control 1 81 51 121 Q  
18 12 22   device control 2 82 52 122 R  
19 13 23   device control 3 83 53 123 S  
20 14 24   device control 4 84 54 124 T  
21 15 25   negative acknowledge 85 55 125 U  
22 16 26   synchronous idle 86 56 126 V  
23 17 27   end of trans. block 87 57 127 W  
24 18 30   cancel 88 58 130 X  
25 19 31   end of medium 89 59 131 Y  
26 1A 32   substitute 90 5A 132 Z  
27 1B 33   escape 91 5B 133 [  
28 1C 34   file separator 92 5C 134 \  
29 1D 35   group separator 93 5D 135 ]  
30 1E 36   record separator 94 5E 136 ^  
31 1F 37   unit separator 95 5F 137 _  
32 20 40   space 96 60 140 `  
33 21 41 !   97 61 141 a  
34 22 42 "   98 62 142 b  
35 23 43 #   99 63 143 c  
36 24 44 $   100 64 144 d  
37 25 45 %   101 65 145 e  
38 26 46 &   102 66 146 f  
39 27 47 '   103 67 147 g  
40 28 50 (   104 68 150 h  
41 29 51 )   105 69 151 i  
42 2A 52 *   106 6A 152 j  
43 2B 53 +   107 6B 153 k  
44 2C 54 ,   108 6C 154 l  
45 2D 55 -   109 6D 155 m  
46 2E 56 .   110 6E 156 n  
47 2F 57 /   111 6F 157 o  
48 30 60 0   112 70 160 p  
49 31 61 1   113 71 161 q  
50 32 62 2   114 72 162 r  
51 33 63 3   115 73 163 s  
52 34 64 4   116 74 164 t  
53 35 65 5   117 75 165 u  
54 36 66 6   118 76 166 v  
55 37 67 7   119 77 167 w  
56 38 70 8   120 78 170 x  
57 39 71 9   121 79 171 y  
58 3A 72 :   122 7A 172 z  
59 3B 73 ;   123 7B 173 {  
60 3C 74 <   124 7C 174 |  
61 3D 75 =   125 7D 175 }  
62 3E 76 >   126 7E 176 ~  
63 3F 77 ?   127 7F 177 DEL  

2- COALESCE

La fonction Oracle SQL COALESCE renvoie la première expression NOT NULL parmi une liste d'expresions en évaluant chaque valeur et en déterminant si celle ci est NULL. Si toutes les expressions de la liste sont NULL, COALESCE retourne une valeur NULL.
Syntaxe: 

COALESCE( p_expression1, p_expression2, ... p_expressionN )
Paramètres:
  • p_expression1, p_expression2, .. p_expressionN
  • Les expressions pour évaluer les valeurs not null.
Exemple: 

-- --> 'Abc'
Select COALESCE(null, 'Abc', '123') as Column1 from dual;


-- --> 'Aaa'
Select COALESCE('Aaa', null, '345') as Column1 from dual;


-- --> 'Sss'
Select COALESCE(null, null, null, 'Sss') as Column1 from dual;

3- UPPER

La fonction UPPER() dans le langage SQL permet de transformer tous les caractères en minuscules dans la chaîne de
caractère p_string en majuscules. 
Syntaxe: 

UPPER( p_string )
Paramètre:
p_string
  • La chaîne (string) va être transformée en majuscules.
Exemple: 

-- --> TOM CAT
Select Upper('Tom Cat') From Dual;

-- --> TOM CAT
Select Upper('tom cat') From Dual;

4- LOWER

La fonction LOWER() permet de transformer tous les caractères de la chaîne p_string en minuscules.
Syntaxe: 

LOWER( p_string )
Paramètre:
p_string
  • La chaîne ( string) va être transformée en minuscules.
Exemple: 

-- --> tom cat
Select Lower('Tom Cat') From Dual;

-- --> tom cat
Select Lower('tom cat') From Dual;

5- INITCAP

La fonction INITCAP permet de transformer en majuscule la première lettre de chaque mot d'une chaîne de caractère (appelée également "title case"). Les autres mots dans la chaîne sont convertis en minuscules. Les mots sont séparés par espace vide (white space) (spaces, tabs, carriage returns, formfeeds, vertical-tabs, newlines)  ou par des caractères qui ne sont pas alphanumérique.  
Syntaxe: 

INITCAP(  p_string )
Paramètre:
p_string
  • Dans l'argument de la chaîne (string), le premier caractère de cette chaîne transforme en majuscule et les autres en minuscules.
     
     
Exemple: 

-- --> Tom Cat
Select Initcap('TOM CAT') From Dual;

-- --> Tom Cat
Select Initcap('tom cat') From Dual;

6- CHR

La fonction CHR() permet de référencer tous les types de caractères ASCII qui correspondent aux critères spécifiques dans le champs ou les enregistrements. Si la clause NCHAR_CS est spécifiée, cette fonction renvoie le caractère correspondant dans l'ensemble de caractères nationales. 
 
Syntaxe: 

CHR( p_number )

CHR( p_number  using nchar_cs)
Paramètre:
p_number
  • Le code NUMBER est utilisé pour tirer le caratère correspondant.
Exemple: 

-- --> @
Select  chr(64) From dual;

-- --> A
Select  chr(65) From dual;

-- --> â
Select  chr(50082) From dual;

-- --> 쎢
Select  chr(50082  using nchar_cs) From dual;

7- CONCAT(Varchar2, Varchar2)

La fonction CONCAT() permet de concaténer les valeur de deux string pour ne former qu’une seule chaîne de caractère.
 
Syntaxe: 

CONCAT( p_string1, p_string2 )
Paramètres:
p_string1
  • La première chaîne (string) est de concaténer.
p_string2
  • La deuxième chaîne (string) est concaténer.
Exemple: 

-- --> Tom Cat
Select Concat('Tom ', 'Cat') From dual;

8- CONCAT(Clob, Clob)

La fonction CONCAT() permet de concaténer les valeurs de deux chaînes pour ne former qu’une seule chaîne.
Syntaxe: 

CONCAT( p_string1, p_string2 )
  • CONCAT(CLOB, NCLOB) returns NCLOB
  • CONCAT(NCLOB, NCHAR) returns NCLOB
  • CONCAT(NCLOB, CHAR) returns NCLOB
  • CONCAT(NCHAR, CLOB) returns NCLOB
Paramètres:
p_string1
  • première chaînes pour concaténer
p_string2
  • deuxième chaînes pour concaténer
Exemple: 

Declare
   C1 Clob := To_Clob('Tom ');
   C2 Clob := To_Clob('Cat');
   C3 Clob;
   C4 Clob;
Begin
   Select Concat(C1
                ,C2)
   Into   C3
   From   Dual;
   ----
   -- --> Tom Cat
   Dbms_Output.Put_Line(C3);
   ----
   C4 := Concat(C1
               ,C2);
   -- --> Tom Cat
   Dbms_Output.Put_Line(C4);
End;

9- INSTR

La fonction INSTR() est utilisée pour obtenir la position d’une occurrence dans une chaîne de caractères. 
Syntaxe: 

INSTR( p_string, p_substring [, p_start_position [,  p_occurrence ] ] )
p_string
  • La chaîne à chercher.Peut-être p_string soit CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, ou NCLOB.
p_substring
  • La sous- chaîne à chercher dans p_string. p_substring peut- être, soit CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, ou NCLOB.
p_start_position
  • Facultatif. La position dans p_string où la recherche est entamée. Si non, il renvoie 1 par défaut. La première position dans la chaîne (string) est 1. Si la clause p_start_position est négative, la fonction INSTR compte la clause p_start_position en arrière, le nombre de la fin de la chaîne, et ensuite recherche vers le début de la chaîne.
p_occurrence
  • Facultatif. Comptez le nombre d'apparition n fois de p_occurrence de p_substring. Si on n'utilse pas ce paramètre, il est 1 par défaut.
La fonction INSTR() va chercher et retourner la position de p_substring dans p_string. Si la chaîne p_substring n’est pas présente dans p_string, alors la fonction retournera la position 0.
 
Exemple: 

-- --> 14
Select Instr('Corporate Floor', 'or', 5, 2) as Column1 from dual;


-- --> 5
Select Instr('Corporate Floor', 'or', -10) as Column1 from dual;
Pour en savoir plus:

10- INSTRB

La fonction INSTRB() obtenir la position d’une occurrence dans une chaîne de caractères. trả về vị trí của chuỗi con trong một chuỗi cho trước, sử dụng các byte thay vì các ký tự.
Syntaxe: 


INSTRB (p_string , p_substring [, p_start_position [, p_occurrence]])
La fonction INSTRB() renvoie la position de la première occurrence d'une chaîne à l'intérieur d'une autre. Au lieu de renvoyer la position du caractère de la première occurrence d'une chaîne à l'intérieur d'une autre, la fonction INSTRB() renvoie la position de l'octet.
Paramètres:
p_string
  • Expression de la chaîne faisant l'objet de recherche.
p_substring
  • Expression de chaîne recherchée.
p_start_position
  • Un nombre entier, non 0, indique la position de l'octet que la fonction commence à faire la recherche. Si la clause p_start_position est négative, docn la fonction INSTRB prend de compte et effectue la recherche à l'inverse qui commence de la fin vers le début. La valeur par défaut 1, signifie une fonction de recherche à la position des premiers octets de la chaîne.
p_occurrence
  • Un nombre entier indique le nombre d'apparition de la chaîne recherchée. Sa valeur doit être positive. Si ce paramètre est omis, les valeurs par défaut de la clause p_occurrence sont 1.
Si la chaîne p_substring n’est pas présente dans p_string, alors la fonction INSTRB retournera la position 0. 

-- --> 6
Select Instrb('Tiếng Việt', 'n') from dual;

-- --> 11
Select Instrb('Tiếng Việt', 'ệ' ,5) from dual;
Pour aller plus ploin:

11- LENGTH

Syntaxe: 

LENGTH( p_string )
Paramètres:
p_string   (CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB)
  • Est une chaîne de caractères qui a besoin de calculer sa longeur. Si la clause p_string est NULL ou vide, alors la fonction LENGTH renvoie NULL
Exemple: 

-- --> 7
Select Length('Tom Cat') As Column1 From Dual;

12- LENGTHB

La fonction LENGTHB() permet de calculer la longueur d’une chaîne de caractères. Elle se base sur le nombre d’octets en remplacant de compter des caractères.
Syntaxe: 

LENGTHB( p_string )
Paramètres:
p_string   (CHAR, VARCHAR2, NCHAR, NVARCHAR2)
  • Le string doit rendre la longueur des caractères (en octet).
Exemple: 

-- Null - Null
Select Lengthb(Null) Column1
      ,Lengthb('') Column2
From   Dual;

-- 7
Select Lengthb('Tom Cat') Column1 From dual;

-- 2
Select Lengthb('Â') As Column1 From Dual;


-- length of characters
-- --> 10
Select Length('Tiếng Việt') from dual;

-- length of bytes
-- --> 14
Select Lengthb('Tiếng Việt') from dual;

13- LPAD

La fonction LPAD() permet de compléter une chaîne de caractère, en ajoutant des caractères au début de cette chaîne.
Syntaxe: 

LPAD( p_string, p_padded_length [, p_pad_string] )
Paramètres:
p_string
  • La chaîne de caractères d’entrée.
p_padded_length
  • La longueur souhaitée. Si la longueur souhaitée de p_padded_length est plus petite la taille actuelle de la chaîne de caractère (p_string), alors la chaîne retournée sera tronquée, la mettre au début, pour faire la taille exacte qui est souhaitée p_padded_length.
p_pad_string
  • Facultatif. C'est la chaîne de compétion au début de p_string. Si ce paramètre est omis, dans la fonction LPAD, la chaîne de complétion sera des espaces vides au début de p_string.
Exemple: 

-- 00123
Select Lpad('123', 5, '0')  From   Dual;

-- 12345
Select Lpad('1234567', 5, '0') From Dual;


-- '       Tom'
Select Lpad('Tom', 10) From   Dual;

-- 'To'
Select Lpad('Tom', 2) From   Dual;

14- LTRIM

La fonction LTRIM() permet de supprimer des espaces vides au début (leading spaces) d’une chaîne de caractères. 
Il est possible de spécifier l'espace vide à supprimer au début de la chaîne de caractère, mais vous pouvez également préciser des caractères à supprimer. Des caractères sont peut- être indiqués comme littéraux ('A', 'm',..) ou CHR en équivalent. Par exemple: 'CHR(112)', 'CHR(68)'.
La fonction LTRIM() renvoie la valeur de VARCHAR2.
Syntaxe: 

LTRIM( p_string [, p_trim_string] )
Paramètres:
p_string
  • La chaîne qui supprime les caractères au début.
p_trim_string
  • Facultatif. C'est la chaîne qui va être supprimée au début de p_string. Si ce paramètre est omis, la fonnction LTRIM va supprimer tous les espaces vides de p_string.
Exemple: 

-- --> TOM
Select LTRIM('   TOM') From dual;

-- --> 789
Select LTRIM('000789', '0') From Dual;


-- --> ABC01
Select LTRIM('0101ABC01', '01') From dual;


-- --> 4210ABC
Select LTRIM('34210ABC', '0123') From dual;

15- RTRIM

La fonction RTRIM() permet de supprimer des espaces vides à la fin  (trailing spaces) d’une chaîne de caractères. Il est possible de spécifier l'espace vide à supprimer au début de la chaîne de caractère, mais il vous permet également de choisir les caractères à supprimer. Des caractères sont peut- être indiqués comme littéraux ('A', 'm',..) ou CHR en équivalent. Par exemple: 'CHR(112)', 'CHR(68)'.
La fonction RTRIM() renvoie de la valeur de VARCHAR2.
Syntaxe: 

RTRIM( p_string [, trim_string ] )
Paramètres:
p_string
  • La chaîne à supprimer tout caractères à la fin.
p_trim_string
  • Faculcatif. C'est la chaîne va être supprimée à la fin de p_string. Si ce paramètre est omis, la fonction RTRIM() va supprimer des espaces vides à la fin de p_string.
Exemple: 

-- --> TOM
Select RTRIM('TOM   ') From dual;

-- --> 000789
Select RTRIM('00078900', '0') From Dual;


-- --> 0101ABC
Select RTRIM('0101ABC0101', '01') From dual;


-- --> ABC34
Select RTRIM('ABC34210', '0123') From dual;

16- TRIM

La fonction TRIM() supprime les espaces au début (leading spaces) et en fin (trailing spaces) de chaîne de caractère. Il est possible de spécifier l'espace vide à supprimer au début de la chaîne de caractère, mais il vous permet également de choisir les caractères à supprimer. Des caractères sont peut- être indiqués comme littéraux ('A', 'm',..) ou CHR en équivalent. Par exemple: 'CHR(112)', 'CHR(68)'.
La fonction TRIM() renvoie de la valeur de VARCHAR2.
Syntaxe: 

TRIM( [ [ LEADING | TRAILING | BOTH ] p_trim_character FROM ] p_string )
Paramètres:
LEADING
  • La fonction va supprimer p_trim_character uniquement en début de chaîne p_string.
TRAILING
  •  La fonction va supprimer p_trim_character uniquement en début de chaîne p_string.
BOTH
  •  La fonction va supprimer p_trim_character en début et en fin de chaîne p_string.
p_trim_character
  •  Les caractères à supprimer de p_string. Si ce paramètre est omis, la fonction TRIM() va supprimer les espaces vides de p_string.
p_string
  • La chaîne à supprimer quelques caractères.
Exemple: 

-- --> TOM
Select TRIM('  TOM   ') From dual;


-- --> 78900
Select TRIM(Leading '0' from '00078900') From Dual;


-- --> 001ABC
Select TRIM(Trailing '0' from '001ABC00') From dual;


-- --> ABC21
Select TRIM(Both '0' from '00ABC21000' ) From dual;

17- SUBSTR

La fonction SUBSTR() (ou SUBSTRING() ) est utilisée pour segmenter une chaîne de caractère (sous- chaîne) d'une chaîne de caractère.
Syntaxe: 

SUBSTR( p_string, p_start_position [, p_length ] )
Paramètres:
p_string
  • La source de la chaîne.
p_start_position
  • La position qui segmente la sous- chaîne. La valeur par défaut est toujours 1.
p_length
  • Facultatif. C'est le nombre des caractères qui ont segmenté. Si ce paramètres est omis, la fonction SUBSTR() retourne la sous- chaîne à partir de la position définie par p_start_position  à la fin de p_string.
  1. Si la valeur de p_start_position est 0, la fonction SUBSTR traite p_start_position étant comme 1 (La première position dans la chaîne p_string).
  2. Si la valeur de p_start_position est positive, la fonction SUBSTR commence à la fin de p_string, et compte à l'envers.
  3. Si la valeur de p_length est négative, la fonction SUBSTR renvoie à la valeur NULL.

-- 'is is'
Select substr('this is a text message',3,5) Column1 from dual;

-- 'is is a text message'
Select substr('this is a text message',3) Column1 from dual;

-- 'messa'
Select substr('this is a text message',-7,5) Column1 from dual;

-- 'message'
Select substr('this is a text message',-7) Column1 from dual;

-- 'age'
Select substr('this is a text message',-3, 5) Column1 from dual;

-- null
Select substr('this is a text message',-3, -5) Column1 from dual;

18- SUBSTRB

Au lieu de renvoyer d'une sous- chaîne, la fonction INSTRB() renvoie la position de l'octet (byte) plus que caractères.
Sytaxe: 

SUBSTRB( p_string, p_start_position [, p_length ] )
Paramètres:
p_string
  • La source de la chaîne.
p_start_position
  • La position de l'extraction. La première position de la chaîne pour extraire des sous- chaîne est toujours 1.
p_length
  • Facultatif. C'est l'octet- byte qui sont extraits. Si ce paramètre est omis, la fonction SUBSTRB retourne la sous-chaîne de p_start_position tà la fin de p_string.
Exemple: 

-- ây là
Select Substrb('Đây là Tiếng Việt', 3, 7) from dual;


-- y là Ti
Select Substr('Đây là Tiếng Việt', 3, 7) from dual;
Peut-être que vous êtes intéressé

Voici des leçons en ligne à part du site web o7planning que nous recommandons. La liste comprend des leçons en ligne et celles en promo.