PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 13.0 » Langage SQL » Fonctions et opérateurs » Fonctions et opérateurs sur des chaînes de caractères

9.4. Fonctions et opérateurs sur des chaînes de caractères

Cette section décrit les fonctions et opérateurs pour l'examen et la manipulation de valeurs de type chaîne de caractères. Dans ce contexte, les chaînes incluent les valeurs de type character, character varying et text. Sauf note explicite, ces fonctions et opérateurs sont déclarés comme acceptant et renvoyant le type text. Ils accepteront directement des arguments de type character varying. Les valeurs de type character seront converties en text avant que la fonction ou l'opérateur ne soit exécuté, résultant en une suppression des espaces en fin de la valeur de type character.

Le standard SQL définit certaines fonctions de chaîne de caractères comme utilisant des mots clés plutôt que des virgules pour séparer les arguments. Les détails sont disponibles dans Tableau 9.9. PostgreSQL fournit aussi des versions de ces fonctions qui utilisent la syntaxe d'appel de fonctions standards (voir Tableau 9.10).

Note

Avant PostgreSQL 8.3, ces fonctions acceptaient aussi silencieusement des valeurs de plusieurs types de données qui ne sont pas des chaînes de caractères, grâce à la présente de conversions implicites de ces types de données vers le type text. Ces conversions ont été supprimées parce qu'elles causaient fréquemment des comportements étonnants. Néanmoins, l'opérateur de concaténation de chaîne (||) accepte toujours des arguments qui ne sont pas des chaînes de caractères, comme montré dans Tableau 9.9. Pour les autres cas, insérez une conversion explicite vers le type text si vous avez besoin de dupliquer le comportement précédent.

Tableau 9.9. Fonctions et opérateurs SQL pour les chaînes de caractères

Fonction/Opérateur

Description

Exemple(s)

text || texttext

Concatène deux chaînes de caractères.

'Post' || 'greSQL'PostgreSQL

text || anynonarraytext

anynonarray || texttext

Convertit l'argument qui n'est pas une chaîne de caractère en texte, puis concatène les deux chapines. (L'argument qui n'est pas une chaîne ne peut pas être un tableau car cela causerait une ambiguité avec les opérateurs || pour les tableaux. Si vous voulez concaténer l'équivalent texte d'un tableau, vous devez préalablement le convertir explicitement vers le type text.)

'Valeur: ' || 42Valeur: 42

text IS [NOT] [form] NORMALIZEDboolean

Vérifie si la chaîne est dans la forme de normalisation Unicode précisée. Le mot clé optionnel form indique la forme : NFC (valeur par défaut), NFD, NFKC ou NFKD. Cette expression peut seulement être utilisée quand l'encodage serveur est UTF8. Il faut noter que la vérification de la normalisation en utilisant cette expression est généralement plus rapide que de normaliser des chaînes déjà normalisées.

U&'\0061\0308bc' IS NFD NORMALIZEDt

bit_length ( text ) → integer

Renvoie le nombre de bits dans la chaîne de caractères (8 fois la valeur de octet_length).

bit_length('jose')32

char_length ( text ) → integer

character_length ( text ) → integer

Renvoie le nombre de caractères dans la chaîne.

char_length('josé')4

lower ( text ) → text

Convertit la chaîne en minuscule, suivant les règles de la locale de la base de données.

lower('TOM')tom

normalize ( text [, form ] ) → text

Convertit la chaîne vers la forme de normalisation Unicode précisée. Le mot clé optionnel form indique la forme : NFC (valeur par défaut), NFD, NFKC ou NFKD. Cette fonction peut seulement être utilisée quand l'encodage serveur est UTF8.

normalize(U&'\0061\0308bc', NFC)U&'\00E4bc'

octet_length ( text ) → integer

Renvoie le nombre d'octets dans la chaîne.

octet_length('josé')5 (if server encoding is UTF8)

octet_length ( character ) → integer

Renvoie le nombre d'octets dans la chaîne. Comme cette version de la fonction accepte directement le type character, il ne supprimera pas les espaces en fin de chaîne.

octet_length('abc '::character(4))4

overlay ( string text PLACING newsubstring text FROM start integer [ FOR count integer ] ) → text

Remplace la sous-chaîne de string qui commence au start-ième caractère et continue pendant count caractères avec newsubstring. Si count est omis, sa valeur par défaut est la longueur de la chaîne newsubstring.

overlay('Txxxxas' placing 'hom' from 2 for 4)Thomas

position ( substring text IN string text ) → integer

Renvoie l'index de début de la chaîne substring dans string, ou zéro si elle n'est pas présente.

position('om' in 'Thomas')3

substring ( string text [ FROM start integer ] [ FOR count integer ] ) → text

Extrait la sous-chaîne de string commençant au start-ième caractère s'il est spécifié, et arrêtant après count caractère s'il est spécifié. Il est nécessaire de fournir au moins un des deux arguments, start et count.

substring('Thomas' from 2 for 3)hom

substring('Thomas' from 3)omas

substring('Thomas' for 2)Th

substring ( string text FROM pattern text ) → text

Extrait la sous-chaîne correspondant à une expression rationnelle POSIX ; voir Section 9.7.3.

substring('Thomas' from '...$')mas

substring ( string text FROM pattern text FOR escape text ) → text

Extrait la sous-chaîne correspondant à l'expression rationnelle SQL ; voir Section 9.7.2.

substring('Thomas' from '%#"o_a#"_' for '#')oma

trim ( [ LEADING | TRAILING | BOTH ] [ characters text ] FROM string text ) → text

Supprime la chaîne la plus longue contenant seulement des caractères parmi characters (un espace par défaut) à partir du début, de la fin ou des deux bouts (BOTH est la valeur par défaut) de string.

trim(both 'xyz' from 'yxTomxx')Tom

trim ( [ LEADING | TRAILING | BOTH ] [ FROM ] string text [, characters text ] ) → text

Ceci est la syntaxe non standard pour trim().

trim(both from 'yxTomxx', 'xyz')Tom

upper ( text ) → text

Convertit la chaîne en majuscule, suivant les règles de la locale de la base de données.

upper('tom')TOM


Des fonctions supplémentaires de manipulation des chaînes de caractères sont disponibles et listées dans Tableau 9.10. Certaines sont utilisées en interne pour implémenter les fonctions de chaîne du standard SQL listées dans Tableau 9.9.

Tableau 9.10. Autres fonctions pour les chaînes de caractères

Fonction

Description

Exemple(s)

ascii ( text ) → integer

Renvoie le code numérique du premier caractère de l'argument. Dans l'encodage UTF8, renvoie le point code Unicode du caractère. Dans les autres encodages multioctets, l'argument doit être un caractère ASCII.

ascii('x')120

btrim ( string text [, characters text ] ) → text

Supprime la plus longue chaîne contenant seulement les caractères compris dans characters (un espace par défaut) au début et à la fin de string.

btrim('xyxtrimyyx', 'xyz')trim

chr ( integer ) → text

Renvoie le caractère avec le code donné. Dans l'encodage UTF8, l'argument est traité qu'un point code Unicode. Dans les autres encodages multi-octets, l'argument doit désigner un caractère ASCII. chr(0) est interdit parce que les types de données texte ne peuvent pas enregistrer ce caractère.

chr(65)A

concat ( val1 "any" [, val2 "any" [, ...] ] ) → text

Concatène les représentations textuelles de tous les arguments. Les arguments NULL sont ignorés.

concat('abcde', 2, NULL, 22)abcde222

concat_ws ( sep text, val1 "any" [, val2 "any" [, ...] ] ) → text

Concatène tous les arguments sauf le premier, avec des séparateurs. Le premier argument est utilisé comme séparateur, et ne doit pas valoir NULL. Les autres arguments NULL sont ignorés.

concat_ws(',', 'abcde', 2, NULL, 22)abcde,2,22

format ( formatstr text [, formatarg "any" [, ...] ] ) → text

Formate les arguments suivant une chaîne de formatage ; voir Section 9.4.1. Cette fonction est similaire à la fonction C sprintf.

format('Hello %s, %1$s', 'World')Hello World, World

initcap ( text ) → text

Convertit la première lettre de chaque mot en majuscule et le reste en minuscule. Les mots sont des séquences de caractères alpha-numériques séparées par des caractères non alpha-numériques.

initcap('hi THOMAS')Hi Thomas

left ( string text, n integer ) → text

Renvoie les n premiers caractères de la chaîne. Si n est négatif, renvoie tous les caractères sauf les |n| derniers.

left('abcde', 2)ab

length ( text ) → integer

Renvoie le nombre de caractères dans la chaîne.

length('jose')4

lpad ( string text, length integer [, fill text ] ) → text

Étend la chaîne string à la longueur length en ajoutant les caractères de la chaîne fill (un espace par défaut). Si la chaîne string est déjà plus longue que length, alors elle est tronquée (à partir de la droite).

lpad('hi', 5, 'xy')xyxhi

ltrim ( string text [, characters text ] ) → text

Supprime la chaîne la plus longue contenant seulement les caractères dans characters (un espace par défaut) à partir du début de string.

ltrim('zzzytest', 'xyz')test

md5 ( text ) → text

Calcule le hachage MD5 de l'argument, le résultat étant écrit en hexadécimal.

md5('abc')900150983cd24fb0​d6963f7d28e17f72

parse_ident ( qualified_identifier text [, strict_mode boolean DEFAULT true ] ) → text[]

Divise qualified_identifier en un tableau d'identifiants, supprimant tout guillemet des identifiants individuels. Par défaut, les caractères supplémentaires après le dernier identifiant sont considérés comme une erreur ; si le second paramètre est false, alors ces caractères supplémentaires sont ignorés. (Ce comportement est utile pour analuser les noms d'objets comme des fonctions.) Il faut noter que cette fonction ne tronque pas les identifiants dont les noms sont trop longs. Si vous voulez cette troncature, vous pouvez convertir le résultat avec le type name[].

parse_ident('"SomeSchema".someTable'){SomeSchema,sometable}

pg_client_encoding ( ) → name

Renvoie le nom de l'encodage client actuel.

pg_client_encoding()UTF8

quote_ident ( text ) → text

Renvoie la chaîne donnée correctement placée entre guillemets comme un identifiant dans une requête SQL. Les guillemets ne sont ajoutés que s'ils sont nécessaires (c'est-à-dire si la chaîne contient des caractères qui ne font pas partie de ceux autorisés habituellement pour les identifiants ou des caractères majuscules). Les guillemets dans le nom sont aussi doublés. Voir aussi Exemple 42.1.

quote_ident('Foo bar')"Foo bar"

quote_literal ( text ) → text

Renvoie la chaîne donnée correctement placée entre guillemets pour être utilisé comme une valeur littérale de type chaîne dans une requête SQL. Les guillemets simples et antislashs présents dans la chaîne sont correctement doublés. Il faut noter que quote_literal renvoie NULL en cas d'argument NULL. Si l'argument pourrait être NULL, quote_nullable est généralement plus indiqué. Voir aussi Exemple 42.1.

quote_literal(E'O\'Reilly')'O''Reilly'

quote_literal ( anyelement ) → text

Convertit la valeur donnée dans le type text et ajoute des guillemets comme pour une valeur. Les guillemets simples et antislashs présents dans la chaîne sont correctement doublés.

quote_literal(42.5)'42.5'

quote_nullable ( text ) → text

Renvoie la chaîne donnée convenablement placée entre guillemets pour être utilisée comme un littéral de type chaîne dans une requête SQL ; ou, si l'argument vaut NULL, renvoie NULL. Les guillemets simples et antislashs présents dans la chaîne sont correctement doublés. Voir aussi Exemple 42.1.

quote_nullable(NULL)NULL

quote_nullable ( anyelement ) → text

Convertit la valeur donnée dans le type text puis la place entre guillemets comme une valeur littérale ; ou, si l'argument vaut NULL, renvoie NULL. Les guillemets simples et antislashs présents dans la chaîne sont correctement doublés.

quote_nullable(42.5)'42.5'

regexp_match ( string text, pattern text [, flags text ] ) → text[]

Renvoie le(s) sous-chaîne(s) capturée(s) résultant de la première correspondance avec une expression rationnelle POSIX vers string ; voir Section 9.7.3.

regexp_match('foobarbequebaz', '(bar)(beque)'){bar,beque}

regexp_matches ( string text, pattern text [, flags text ] ) → setof text[]

Renvoie le(s) sous-chaîne(s) capturée(s) résultant de la correspondance d'une expression rationnelle POSIX vers string ; voir Section 9.7.3.

regexp_matches('foobarbequebaz', 'ba.', 'g')

 {bar}
 {baz}

regexp_replace ( string text, pattern text, replacement text [, flags text ] ) → text

Remplace le(s) sous-chaîne(s) correspondant à une expression rationnelle POSIX ; voir Section 9.7.3.

regexp_replace('Thomas', '.[mN]a.', 'M')ThM

regexp_split_to_array ( string text, pattern text [, flags text ] ) → text[]

Divise string en utilisant une expression rationnelle POSIX comme délimiteur ; voir Section 9.7.3.

regexp_split_to_array('hello world', '\s+'){hello,world}

regexp_split_to_table ( string text, pattern text [, flags text ] ) → setof text

Divise string en utilisant une expression rationnelle POSIX comme délimiteur ; voir Section 9.7.3.

regexp_split_to_table('hello world', '\s+')

 hello
 world

repeat ( string text, number integer ) → text

Répète string le nombre de fois spécifié par number.

repeat('Pg', 4)PgPgPgPg

replace ( string text, from text, to text ) → text

Remplace toutes les occurrences dans string de la sous-chaîne from avec la sous-chaîne to.

replace('abcdefabcdef', 'cd', 'XX')abXXefabXXef

reverse ( text ) → text

Inverse l'ordre des caractères dans la chaîne.

reverse('abcde')edcba

right ( string text, n integer ) ) → text

Renvoie les n derniers caractères dans la chaîne, ou, si n est négatif, renvoie tous les caractères sauf les |n| premiers caractères.

right('abcde', 2)de

rpad ( string text, length integer [, fill text ] ) ) → text

Étend string à la longueur length en ajoutant les caractères fill (un espace par défaut). Si string est déjà plus long que length, alors elle est tronquée.

rpad('hi', 5, 'xy')hixyx

rtrim ( string text [, characters text ] ) → text

Supprime la plus longue chaîne contenant seulement les caractères compris dans characters (un espace par défaut) à partir de la fin de string.

rtrim('testxxzx', 'xyz')test

split_part ( string text, delimiter text, n integer ) → text

Divise string à chaque occurence de delimiter et renvoie le n-ième champ (en comptant à partir de 1).

split_part('abc~@~def~@~ghi', '~@~', 2)def

strpos ( string text, substring text ) → integer

Renvoie l'index de début de la chaîne substring spécifié à l'intérieur de string, ou zéro si elle n'est pas présente. (Identique à position(substring in string), mais notez l'ordre inversé des arguments.)

strpos('high', 'ig')2

substr ( string text, start integer [, count integer ] ) → text

Extrait la sous-chaîne de string en commençant au start-ième caractère, et étendant de count caractères si ce dernier est spécifié. (Identique à substring(string from start for count).)

substr('alphabet', 3)phabet

substr('alphabet', 3, 2)ph

starts_with ( string text, prefix text ) → boolean

Renvoie true si string commence avec prefix.

starts_with('alphabet', 'alph')t

to_ascii ( string text ) → text

to_ascii ( string text, encoding name ) → text

to_ascii ( string text, encoding integer ) → text

Convertit string en ASCII à partir d'un autre encodage, qui peut être identifié par nom ou numéro. Si encoding est omis, l'encodage de la base est utilisé (ce qui, en réalité, est le seul cas utile). La conversion consiste principalement en la suppression des accents. La conversion est seulement supportée à partir des encodages LATIN1, LATIN2, LATIN9 et WIN1250. (Voir le module unaccent pour une autre solution, plus flexible.)

to_ascii('Karél')Karel

to_hex ( integer ) → text

to_hex ( bigint ) → text

Convertit le nombre en sa représentation hexadécimale équivalente.

to_hex(2147483647)7fffffff

translate ( string text, from text, to text ) → text

Remplace chaque caractère de string qui correspond à un caractère dans l'ensemble from avec le caractère correspondant dans l'ensemble to. Si from est plus long que to, les occurrences des caractères supplémentaires dans from sont supprimées.

translate('12345', '143', 'ax')a2x5


Les fonctions concat, concat_ws et format ont un nombre d'arguments variables, donc il est possible de passer les valeurs à concaténer ou à formater sous la forme d'un tableau marqué avec le mot-clé VARIADIC (voir Section 37.5.5). Les éléments du tableau sont traités comme s'ils étaient des arguments séparés dans la fonction. Si l'argument tableau est NULL, concat et concat_ws renvoient NULL, mais format traite un NULL comme un tableau à zéro élément.

Voir aussi la fonction d'agrégat string_agg in Section 9.21, et les fonctions de conversions entre des chaînes de caractères et le type bytea dans Tableau 9.13.

9.4.1. format

La fonction format produit une sortie formatée suivant une chaîne de formatage, dans un style similaire à la fonction C sprintf.

format(formatstr text [, formatarg "any" [, ...] ])

formatstr est une chaîne de formatage qui indique comment le résultat doit être formaté. Le texte de la chaîne de formatage est copié directement dans le résultat, à l'exception des jokers de format. Les jokers de format agissent comme des espaces réservés dans le chaîne définissant comment les arguments de la fonction doivent être formatés et insérés dans le résultat. Chaque argument formatarg est converti en texte suivant les règles d'affichage habituel pour son type de données, puis formaté et inséré dans la chaîne résultante suivant le joker de format.

Les jokers de format commencent par un caractère % et ont la forme suivante

%[position][flags][width]type

où les champs composants sont :

position (optionnel)

Une chaîne de la forme n$n est l'index de l'argument à afficher. L'index 1 correspond au premier argument après formatstr. Si position est omis, la valeur par défaut est d'utiliser le prochain argument en séquence.

flags (optionnel)

Des options supplémentaires contrôlant comme la sortie du joker est formatée. Actuellement, le seul drapeau supporté est un signe moins (-) qui causera l'alignement à gauche de la valeur du champ. Ceci n'a d'effet que si width est précisé.

width (optionnel)

Indique le nombre minimum de caractères à utiliser pour afficher la sortie du joker. La sortie est remplie à gauche ou à droite (suivant la présence du drapeau -) avec autant d'espaces que nécessaire pour remplir la largeur indiquée. Une largeur trop petite ne cause pas la trincature de la sortie, mais est tout simplement ignorée. La largeur pourrait être indiquée en utilisant soit un entier positif, soit une astérisque (*) pour utiliser le prochain argument de la fonction soit une chaîne de la forme *n$ pour utiliser le n-ième argument de la fonction comme information de largeur.

Si la largeur vient d'un argument de la fonction, cet argument est consommé avant l'argument utilisé pour la valeur du joker de format. Si l'argument de largeur est négatif, le résultat est un alignement à gauche (comme si le drapeau - avait été précisé) à l'intéroeur du champ de longueur abs(width).

type (requis)

Le type de conversion de format à utiliser pour produire le résultat du joker de format. Les types suivants sont supportés :

  • s formate ma valeur en argument comme une simple chaîne. Une valeur NULL est traitée comme une chaîne vide.

  • I traite la valeur en argument comme un identifiant SQL, la plaçant entre guillemets doubles si nécessaire. Une valeur NULL est traitée comme une erreur (équivalent à quote_ident).

  • L traite la valeur en argument comme un littérale SQL. Une valeur NULL est affichée comme la chaîne NULL, sans guillemets (équivalent à quote_nullable).

En plus des jokers de format décrits ci-dessus, la séquence spéciale %% est utilisable pour ajouter un caractère %.

Voici quelques exemples des conversions basiques de format :

SELECT format('Hello %s', 'World');
Résultat : Hello World

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
Résultat : Testing one, two, three, %

SELECT format('INSERT INTO %I VALUES(%L)', 'Foo bar', E'O\'Reilly');
Résultat : INSERT INTO "Foo bar" VALUES('O''Reilly')

SELECT format('INSERT INTO %I VALUES(%L)', 'locations', 'C:\Program Files');
Résultat : INSERT INTO locations VALUES('C:\Program Files')

Voici quelques exemples d'utilisation du champ width et du drapeau - :

SELECT format('|%10s|', 'foo');
Résultat : |       foo|

SELECT format('|%-10s|', 'foo');
Résultat : |foo       |

SELECT format('|%*s|', 10, 'foo');
Résultat : |       foo|

SELECT format('|%*s|', -10, 'foo');
Résultat : |foo       |

SELECT format('|%-*s|', 10, 'foo');
Résultat : |foo       |

SELECT format('|%-*s|', -10, 'foo');
Résultat : |foo       |

Ces exemples montrent l'utilisation du champ position :

SELECT format('Testing %3$s, %2$s, %1$s', 'one', 'two', 'three');
Résultat : Testing three, two, one

SELECT format('|%*2$s|', 'foo', 10, 'bar');
Résultat : |       bar|

SELECT format('|%1$*2$s|', 'foo', 10, 'bar');
Résultat : |       foo|

Contrairement à la fonction C standard sprintf, la fonction format de PostgreSQL autorise les jokers de format sans et avec des champs position mixés dans la même champs de formatage. Un joker de format sans champ position utilise toujours le prochain argument après le dernier argument consommé. De plus, la fonction format ne requiert pas que tous les arguments de la fonction soient utilisés dans la chaîne de format. Par exemple :

SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Résultat : Testing three, two, three

Les jokers de format %I et %L sont particulièrement utiles pour construire en toute sécurité des requêtes SQL dynamiques. Voir Exemple 42.1.