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

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_substringn’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.
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).
Si la valeur de p_start_position est positive, la fonction SUBSTR commence à la fin de p_string, et compte à l'envers.
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;

Tutoriels de base de données Oracle

Show More