Fonctions de chaîne

Ces fonctions de chaîne acceptent deux types de valeurs différents : les types de données STRING et BYTES. Les valeurs de type STRING doivent être des chaînes UTF-8 correctement formées.

Les fonctions qui renvoient des valeurs de position, par exemple STRPOS, encodent ces positions au format INT64. La valeur 1 fait référence au premier caractère (ou octet), la valeur 2 au deuxième, et ainsi de suite. La valeur 0 indique un index non valide. Lorsque le traitement concerne des valeurs de type STRING, les positions renvoyées font référence aux positions des caractères.

Toutes les comparaisons de chaînes sont effectuées octet par octet, sans prise en compte de l'équivalence canonique Unicode.

BYTE_LENGTH

BYTE_LENGTH(value)

Description

Renvoie la longueur de la valeur en octets, qu'il s'agisse d'une valeur de type STRING ou BYTES.

Type renvoyé

INT64

Exemples

WITH example AS
  (SELECT "абвгд" AS characters, b"абвгд" AS bytes)

SELECT
  characters,
  BYTE_LENGTH(characters) AS string_example,
  bytes,
  BYTE_LENGTH(bytes) AS bytes_example
FROM example;

+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд      | 10             | абвгд | 10            |
+------------+----------------+-------+---------------+

CHAR_LENGTH

CHAR_LENGTH(value)

Description

Renvoie la longueur de la STRING en caractères.

Type renvoyé

INT64

Exemples

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHAR_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHARACTER_LENGTH

CHARACTER_LENGTH(value)

Description

Synonyme de CHAR_LENGTH.

Type renvoyé

INT64

Exemples

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  CHARACTER_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_values)

Description

Accepte en entrée un tableau de points de code ASCII étendu (tableau de valeurs INT64) et renvoie une valeur de type BYTES.

Pour découvrir comment convertir une valeur de type BYTES en un tableau de points de code, consultez la section TO_CODE_POINTS.

Type renvoyé

BYTES

Exemples

Voici un exemple de base utilisant CODE_POINTS_TO_BYTES.

SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;

+-------+
| bytes |
+-------+
| AbCd  |
+-------+

L'exemple suivant utilise un algorithme ROT13 (décalage de 13 caractères pour chaque lettre) pour encoder une chaîne.

SELECT CODE_POINTS_TO_BYTES(ARRAY(
  (SELECT
      CASE
        WHEN chr BETWEEN b'a' AND b'z'
          THEN TO_CODE_POINTS(b'a')[offset(0)] +
            MOD(code + 13 - TO_CODE_POINTS(b'a')[offset(0)],26)
        WHEN chr BETWEEN b'A' AND b'Z'
          THEN TO_CODE_POINTS(b'A')[offset(0)] +
            MOD(code + 13 - TO_CODE_POINTS(b'A')[offset(0)],26)
        ELSE code
      END
   FROM
     (SELECT code, CODE_POINTS_TO_BYTES(ARRAY[code]) AS chr
      FROM UNNEST(TO_CODE_POINTS(input)) AS code WITH OFFSET
      ORDER BY OFFSET)
   ))) AS encoded_string
FROM UNNEST(ARRAY['Test String!']) AS input;

+----------------+
| encoded_string |
+----------------+
| Grfg Fgevat!   |
+----------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

Description

Accepte en entrée un tableau de points de code Unicode (tableau de valeurs INT64) et renvoie une valeur de type STRING.

Pour découvrir comment convertir une valeur de type STRING en un tableau de points de code, consultez la section TO_CODE_POINTS.

Type renvoyé

STRING

Exemple

Voici un exemple de base utilisant CODE_POINTS_TO_STRING.

SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+

L'exemple suivant calcule la fréquence des lettres dans un ensemble de mots.

WITH Words AS (
  SELECT word
  FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word
)
SELECT
  CODE_POINTS_TO_STRING([code_point]) AS letter,
  COUNT(*) AS letter_count
FROM Words,
  UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;

+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a      | 5            |
| f      | 3            |
| r      | 2            |
| b      | 2            |
| l      | 2            |
| o      | 2            |
| g      | 1            |
| z      | 1            |
| e      | 1            |
| m      | 1            |
| i      | 1            |
+--------+--------------+

CONCAT

CONCAT(value1[, ...])

Description

Concatène une ou plusieurs valeurs STRING ou BYTE dans un seul résultat.

La fonction renvoie NULL si l'un des arguments d'entrée est NULL.

Remarque : Vous pouvez également utiliser l'opérateur de concaténation || pour concaténer les valeurs en une chaîne.

Type renvoyé

STRING ou BYTES

Exemples

SELECT CONCAT("T.P.", " ", "Bar") as author;

+---------------------+
| author              |
+---------------------+
| T.P. Bar            |
+---------------------+

With Employees AS
  (SELECT
    "John" AS first_name,
    "Doe" AS last_name
  UNION ALL
  SELECT
    "Jane" AS first_name,
    "Smith" AS last_name
  UNION ALL
  SELECT
    "Joe" AS first_name,
    "Jackson" AS last_name)

SELECT
  CONCAT(first_name, " ", last_name)
  AS full_name
FROM Employees;

+---------------------+
| full_name           |
+---------------------+
| John Doe            |
| Jane Smith          |
| Joe Jackson         |
+---------------------+

ENDS_WITH

ENDS_WITH(value1, value2)

Description

Prend en considération deux valeurs. Renvoie TRUE si la deuxième valeur est un suffixe de la première.

Type renvoyé

BOOL

Exemples

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  ENDS_WITH(item, "e") as example
FROM items;

+---------+
| example |
+---------+
|    True |
|   False |
|    True |
+---------+

FORMAT

Cloud Spanner SQL accepte une fonction FORMAT() pour la mise en forme des chaînes. Cette fonction est semblable à la fonction C printf. Elle génère une STRING à partir d'une chaîne de format contenant zéro, un ou plusieurs spécificateurs de format, ainsi qu'une liste (de longueur variable) d'arguments supplémentaires correspondant aux spécificateurs de format. Voici quelques exemples :

Description	Instruction	Résultat
Entier simple	FORMAT("%d", 10)	10
Entier avec espaces de remplissage à gauche	FORMAT("|%10d|", 11)	|           11|
Entier avec zéros de remplissage à gauche	FORMAT("+%010d+", 12)	+0000000012+
Entier avec virgules	FORMAT("%'d", 123456789)	123,456,789
STRING	FORMAT("-%s-", 'abcd efg')	-abcd efg-
FLOAT64	FORMAT("%f %E", 1.1, 2.2)	1.100000 2.200000E+00
DATE	FORMAT("%t", date "2015-09-01")	2015-09-01
TIMESTAMP	FORMAT("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles")	2015‑09‑01 19:34:56+00

La fonction FORMAT() ne fournit pas une mise en forme entièrement personnalisable pour tous les types et valeurs, ni une mise en forme prenant en compte les paramètres régionaux.

Si une mise en forme personnalisée est nécessaire pour un type, vous devez d'abord formater celui-ci à l'aide de fonctions spécifiques au type, telles que FORMAT_DATE() ou FORMAT_TIMESTAMP(). Par exemple :

SELECT FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));

Renvoie :

date: January 02, 2015!

Syntaxe

La syntaxe de FORMAT() accepte en entrée une chaîne de format et une liste d'arguments de longueur variable, puis produit un résultat STRING :

FORMAT(<format_string>, ...)

L'expression <format_string> peut contenir zéro, un ou plusieurs spécificateurs de format. Chaque spécificateur de format est introduit par le symbole % et doit être mappé sur un ou plusieurs des arguments restants. Dans la plupart des cas, il s'agit d'un mappage de type "un à un", sauf lorsque le spécificateur * est présent. Par exemple, %.*i correspond à deux arguments : un argument de longueur et un argument de nombre entier signé. Si le nombre d'arguments liés aux spécificateurs de format n'est pas identique au nombre total d'arguments, une erreur se produit.

Spécificateurs de format acceptés

Le spécificateur de format de la fonction FORMAT() suit ce prototype :

%[flags][width][.precision]specifier

Les spécificateurs de format acceptés sont identifiés dans le tableau ci-après. Les écarts par rapport à printf() sont indiqués en italique.

Spécificateur	Description	Exemples	Types
d ou i	Entier décimal	392	INT64
o	Octal	610	INT64*
x	Entier hexadécimal	7fa	INT64*
X	Entier hexadécimal (majuscule)	7FA	INT64*
f	Notation décimale, en [-](partie entière).(partie fractionnaire) pour les valeurs finies et en minuscules pour les valeurs non finies	392,650000 inf nan	FLOAT64
F	Notation décimale, en [-](partie entière).(partie fractionnaire) pour les valeurs finies et en majuscules pour les valeurs non finies	392,650000 INF NAN	FLOAT64
e	Notation scientifique (mantisse/exposant), minuscule	3,926500e+02 inf nan	FLOAT64
E	Notation scientifique (mantisse/exposant), majuscule	3,926500E+02 INF NAN	FLOAT64
g	Notation décimale ou scientifique, en fonction de l'exposant de la valeur d'entrée et de la précision spécifiée. Minuscule. Consultez la section Comportement de %g et %G pour plus de détails.	392,65 3.9265e+07 inf nan	FLOAT64
G	Notation décimale ou scientifique, en fonction de l'exposant de la valeur d'entrée et de la précision spécifiée. Majuscule. Consultez la section Comportement de %g et %G pour plus de détails.	392,65 3.9265E+07 INF NAN	FLOAT64
s	Chaîne de caractères	exemple	STRING
t	Renvoie une chaîne imprimable représentant la valeur. Ce résultat est souvent semblable à celui du casting de l'argument au format STRING. Voir Comportement de %t et de %T.	sample 2014‑01‑01	<tous>
T	Génère une chaîne qui est une constante Cloud Spanner SQL valide, dont le type est semblable à celui de la valeur (il peut contenir plus de caractères ou se présenter sous forme de chaîne). Voir Comportement de %t et de %T.	'sample' b'bytes sample' 1234 2.3 date '2014‑01‑01'	<tous>
%	'%%' produit un seul '%'	%	n/a

^*Les indicateurs %o, %x et %X génèrent une erreur si des valeurs négatives sont utilisées.

Le spécificateur de format peut éventuellement contenir les sous-spécificateurs identifiés ci-dessus dans le prototype de spécificateur.

Ces sous-spécificateurs doivent être conformes aux spécifications suivantes.

Options

Options	Description
-	Justifie à gauche dans la largeur de champ donnée ; la justification à droite est la valeur par défaut (consultez la rubrique relative aux sous-spécificateurs de largeur).
+	Force l'ajout d'un préfixe "plus" ou "moins" (signe + ou -) avant le résultat, même pour les nombres positifs. Par défaut, seuls les nombres négatifs sont précédés d'un signe -.
<espace>	Si aucun signe ne doit être écrit par la suite, un espace est inséré avant la valeur
#	Pour "%o", "%x" et "%X", cet indicateur signifie faire précéder la valeur de 0, 0x ou 0X respectivement pour les valeurs différentes de zéro. Pour "%f", "%F", "%e" et "%E", cet indicateur signifie ajouter le point décimal même s'il n'y a pas de fraction, sauf si la valeur n'est pas finie. Pour "%g" et "%G", cet indicateur signifie ajouter le point décimal même s'il n'y a pas de fraction, sauf si la valeur n'est pas finie, et ne jamais supprimer les zéros de fin après le point décimal.
0	Ajoute des zéros (0) au lieu d'espaces à gauche du nombre lorsque l'option de remplissage est spécifiée (consultez la rubrique relative aux sous-spécificateurs de largeur).
'	Formate les entiers en utilisant le caractère de regroupement approprié. Par exemple : FORMAT("%'d", 12345678) renvoie 12,345,678 FORMAT("%'x", 12345678) renvoie bc:614e FORMAT("%'o", 55555) renvoie 15,4403 Cet indicateur ne concerne que les valeurs décimales, hexadécimales et octales.

Les indicateurs peuvent être spécifiés dans n'importe quel ordre. Les options en double ne sont pas considérées comme des erreurs. Les options non pertinentes pour un type d'élément sont ignorées.

Largeur

Largeur	Description
<nombre>	Nombre minimum de caractères à imprimer. Si la valeur à imprimer ne contient pas le nombre minimum de caractères requis, le résultat est complété par des espaces. La valeur n'est pas tronquée, même si le résultat contient plus de caractères.
*	La largeur n'est pas spécifiée dans la chaîne de format, mais en tant qu'argument de valeur entière supplémentaire précédant l'argument à formater.

Précision

Précision	Description
.<nombre>	Pour les spécificateurs d'entiers "%d", "%i", "%o", "%u", "%x" et "%X" : la précision indique le nombre minimal de chiffres à écrire. Si la valeur à écrire comporte moins de chiffres, des zéros sont ajoutés à la fin du résultat. La valeur n'est pas tronquée, même si le résultat contient plus de chiffres. Une précision de 0 signifie qu'aucun caractère n'est écrit pour la valeur 0. Pour les spécificateurs "%a", "%A", "%e", "%E", "%f" et "%F" : il s'agit du nombre de chiffres à imprimer après le point décimal. La valeur par défaut est 6. Pour les spécificateurs "%g" et "%G" : il s'agit du nombre de chiffres significatifs à imprimer, avant la suppression des zéros de fin après le point décimal. La valeur par défaut est 6.
.*	La précision n'est pas spécifiée dans la chaîne de format, mais en tant qu'argument de valeur entière supplémentaire précédant l'argument à formater.

Comportement de %g et de %G

Les spécificateurs de format %g et %G choisissent la notation décimale (comme les spécificateurs %f et %F) ou la notation scientifique (comme les spécificateurs %e et %E), en fonction de l'exposant de la valeur d'entrée et de la précision spécifiée.

Soit p la précision spécifiée (6 par défaut ; 1 si la précision spécifiée est inférieure à 1). La valeur d'entrée est d'abord convertie en notation scientifique avec la précision (p - 1). Si l'exposant x obtenu est inférieur à -4 ou supérieur ou égal à p, c'est la notation scientifique avec la précision (p - 1) qui est retenue. Dans les autres cas, c'est la notation décimale avec la précision (p - 1 - x) qui est utilisée.

À moins que l'indicateur # ne soit présent, les zéros de fin après le point décimal sont supprimés, et le point décimal est également supprimé si aucun chiffre ne figure après.

Comportement de %t et %T

Les spécificateurs de format %t et %T sont définis pour tous les types. La largeur, la précision et les indicateurs agissent comme pour %s : la largeur correspond à la largeur minimale et la STRING est complétée pour atteindre cette taille. La précision est la largeur maximale du contenu à afficher, la STRING est tronquée à cette taille avant que le remplissage en largeur ne soit appliqué.

Le spécificateur %t est toujours destiné à être une forme lisible de la valeur.

Le spécificateur %T est toujours un littéral SQL valide d'un type similaire, tel qu'un type numérique plus large. Le littéral n'inclura pas de castings ni de nom de type, sauf dans le cas particulier des valeurs à virgule flottante non finies.

La STRING est formatée comme suit :

Type	%t	%T
NULL de n'importe quel type	NULL	NULL
INT64	123	123
FLOAT64	123.0 (toujours avec .0) 123e+10 inf -inf NaN	123.0 (toujours avec .0) 123e+10 CAST("inf" en tant que <type>) CAST("-inf" en tant que <type>) CAST("nan" en tant que <type>)
STRING	Valeur de chaîne sans guillemets	Littéral de chaîne entre guillemets
BYTES	Octets échappés sans guillemets par exemple, abc\x01\x02	Littéral d'octets entre guillemets par exemple, b"abc\x01\x02"
DATE	2011-02-03	DATE "2011-02-03"
TIMESTAMP	2011-02-03 04:05:06+00	TIMESTAMP "2011-02-03 04:05:06+00"
ARRAY	[valeur, valeur, …] où les valeurs sont formatées avec %t	[valeur, valeur, …] où les valeurs sont formatées avec %T

Conditions d'erreur

Si un spécificateur de format n'est pas valide, s'il n'est pas compatible avec le type d'argument associé, ou encore si un nombre erroné ou des arguments incorrects sont fournis, une erreur est générée. Par exemple, les expressions <format_string> suivantes ne sont pas valides :

FORMAT('%s', 1)

FORMAT('%')

Gérer des arguments NULL

Une chaîne de format NULL donne une STRING de sortie NULL. Tous les autres arguments sont ignorés dans ce cas.

La fonction produit généralement une valeur NULL si un argument NULL est présent. Par exemple, FORMAT('%i', <NULL expression>) génère une STRING de valeur NULL en sortie.

Il existe toutefois des exceptions : si le spécificateur de format est %t ou %T (tous deux produisent des STRING qui correspondent effectivement aux sémantiques de la valeur littérale et de CAST), une valeur NULL produit un résultat "NULL" (sans les guillemets) dans la STRING résultante. Par exemple, la fonction :

FORMAT('00-%t-00', <NULL expression>);

Renvoie

00-NULL-00

Règles sémantiques supplémentaires

Les valeurs FLOAT64 peuvent être +/-inf ou NaN. Lorsqu'un argument comporte l'une de ces valeurs, le résultat des spécificateurs de format %f, %F, %e, %E, %g, %G et %t est inf, -inf ou nan (ou le même terme en majuscules) selon le cas. Ceci est cohérent avec la manière dont Cloud Spanner SQL convertit (par casting) ces valeurs en STRING. Pour %T, Cloud Spanner SQL renvoie des chaînes entre guillemets pour les valeurs FLOAT64 dont les représentations littérales ne peuvent être que des chaînes.

FROM_BASE64

FROM_BASE64(string_expr)

Description

Convertit l'entrée string_expr encodée en base64 au format BYTES. Pour convertir une valeur de type BYTES en valeur de type STRING encodée en base64, utilisez TO_BASE64.

Type renvoyé

BYTES

Exemple

SELECT FROM_BASE64('3q2+7w==') AS byte_data;

-- Note that the result of FROM_BASE64 is of type BYTES, displayed as a base64-encoded string.
+-----------+
| byte_data |
+-----------+
| 3q2+7w==  |
+-----------+

FROM_HEX

FROM_HEX(string)

Description

Convertit une STRING encodée en hexadécimal au format BYTES. Renvoie une erreur si la valeur d'entrée de type STRING contient des caractères non compris dans la plage (0..9, A..F, a..f). La casse des lettres (majuscule/minuscule) n'a pas d'importance. Si l'entrée STRING contient un nombre impair de caractères, la fonction agit comme si l'entrée commençait par un 0 supplémentaire. Pour convertir une valeur BYTES en STRING encodée en hexadécimal, utilisez TO_HEX.

Type renvoyé

BYTES

Exemple

WITH Input AS (
  SELECT '00010203aaeeefff' AS hex_str UNION ALL
  SELECT '0AF' UNION ALL
  SELECT '666f6f626172'
)
SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM Input;

-- Note that the result of FROM_HEX is of type BYTES, displayed as a base64-encoded string.
+------------------+--------------+
| hex_str          | bytes_str    |
+------------------+--------------+
| 0AF              | AAECA6ru7/8= |
| 00010203aaeeefff | AK8=         |
| 666f6f626172     | Zm9vYmFy     |
+------------------+--------------+

LENGTH

LENGTH(value)

Description

Renvoie la longueur de la valeur. La valeur renvoyée correspond au nombre de caractères pour les arguments STRING et au nombre d'octets pour les arguments BYTES.

Type renvoyé

INT64

Exemples

WITH example AS
  (SELECT "абвгд" AS characters)

SELECT
  characters,
  LENGTH(characters) AS string_example,
  LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;

+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд      |              5 |            10 |
+------------+----------------+---------------+

LPAD

LPAD(original_value, return_length[, pattern])

Description

Renvoie une valeur composée de l'élément original_value et du préfixe pattern. Le paramètre return_length est une valeur INT64 qui indique la longueur de la valeur renvoyée. Si original_value est de type BYTES, return_length correspond au nombre d'octets. Si original_value est de type STRING, return_length correspond au nombre de caractères.

La valeur par défaut de pattern est une espace.

original_value et pattern doivent correspondre au même type de données.

Si return_length est inférieur ou égal à la longueur original_value, cette fonction renvoie la valeur original_value tronquée à la valeur de return_length. Par exemple, LPAD("hello world", 7); renvoie "hello w".

Si original_value, return_length ou pattern est NULL, cette fonction renvoie NULL.

Cette fonction renvoie une erreur si :

• return_length est une valeur négative ;
• pattern est vide.

Type renvoyé

STRING ou BYTES

Exemples

SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);

t	len	LPAD
abc	5	"  abc"
abc	2	"ab"
例子	4	"  例子"

SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);

t	len	pattern	LPAD
abc	8	def	"defdeabc"
abc	5	-	"--abc"
例子	5	中文	"中文中例子"

SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);

t	len	LPAD
b"abc"	5	b"  abc"
b"abc"	2	b"ab"
b"\xab\xcd\xef"	4	b" \xab\xcd\xef"

SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);

t	len	pattern	LPAD
b"abc"	8	b"def"	b"defdeabc"
b"abc"	5	b"-"	b"--abc"
b"\xab\xcd\xef"	5	b"\x00"	b"\x00\x00\xab\xcd\xef"

LOWER

LOWER(value)

Description

Pour les arguments STRING, cette fonction renvoie la chaîne d'origine avec tous les caractères alphabétiques en minuscules. Le mappage entre minuscules et majuscules est effectué en fonction de la base de données de caractères Unicode, sans prise en compte des mappages spécifiques à la langue.

Les arguments BYTES sont quant à eux traités comme du texte ASCII et les octets supérieurs à 127 ne sont pas modifiés.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT
    "FOO" as item
  UNION ALL
  SELECT
    "BAR" as item
  UNION ALL
  SELECT
    "BAZ" as item)

SELECT
  LOWER(item) AS example
FROM items;

+---------+
| example |
+---------+
| foo     |
| bar     |
| baz     |
+---------+

LTRIM

LTRIM(value1[, value2])

Description

Identique à TRIM, mais ne supprime que les caractères de début.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", LTRIM(item), "#") as example
FROM items;

+-------------+
| example     |
+-------------+
| #apple   #  |
| #banana   # |
| #orange   # |
+-------------+

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  LTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| apple***  |
| banana*** |
| orange*** |
+-----------+

WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  LTRIM(item, "xyz") as example
FROM items;

+-----------+
| example   |
+-----------+
| applexxx  |
| bananayyy |
| orangezzz |
| pearxyz   |
+-----------+

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regexp)

Description

Renvoie TRUE si value est une correspondance partielle de l'expression régulière (regexp).

Si l'argument regexp n'est pas valide, la fonction renvoie une erreur.

Vous pouvez rechercher une correspondance complète en utilisant ^ (début du texte) et $ (fin du texte). En raison de la priorité des opérateurs d'expression régulière, il est recommandé d'utiliser des parenthèses pour tout élément compris entre ^ et $.

Remarque : La bibliothèque re2 permet d'utiliser des expressions régulières dans Cloud Spanner SQL. Consultez la documentation de cette bibliothèque pour plus d'informations sur la syntaxe d'expression régulière à utiliser.

Type renvoyé

BOOL

Exemples

SELECT
  email,
  REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
  (SELECT
    ["[e-mail]", "[e-mail]", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| [e-mail] | true     |
| [e-mail] | true     |
| www.example.net | false    |
+-----------------+----------+

# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
    AS valid_email_address,
  REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
    AS without_parentheses
FROM
  (SELECT
    ["[e-mail]", "[e-mail]", "[e-mail]", "![e-mail]", "[e-mail]"]
    AS addresses),
  UNNEST(addresses) AS email;

+----------------+---------------------+---------------------+
| email          | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| [e-mail]      | true                | true                |
| [e-mail] | false               | true                |
| [e-mail]      | true                | true                |
| ![e-mail]     | false               | true                |
| [e-mail]      | false               | false               |
+----------------+---------------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regexp)

Description

Renvoie la première sous-chaîne de value qui correspond à l'expression régulière (regexp). Renvoie NULL s'il n'y a pas de correspondance.

Si l'expression régulière comporte un groupe de capture, la fonction renvoie uniquement la sous-chaîne à laquelle correspond ce groupe de capture. Si l'expression ne contient pas de groupe de capture, la fonction renvoie l'ensemble de la sous-chaîne correspondante.

Renvoie une erreur si :

• l'expression régulière n'est pas valide ;
• l'expression régulière comporte plus d'un groupe de capture.

Remarque : La bibliothèque re2 permet d'utiliser des expressions régulières dans Cloud Spanner SQL. Consultez la documentation de cette bibliothèque pour plus d'informations sur la syntaxe d'expression régulière à utiliser.

Type renvoyé

STRING ou BYTES

Exemples

WITH email_addresses AS
  (SELECT "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
  AS user_name
FROM email_addresses;

+-----------+
| user_name |
+-----------+
| foo       |
| bar       |
| baz       |
+-----------+

WITH email_addresses AS
  (SELECT "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" as email
  UNION ALL
  SELECT "[e-mail]" as email)

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
  AS top_level_domain
FROM email_addresses;

+------------------+
| top_level_domain |
+------------------+
| com              |
| org              |
| net              |
+------------------+

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value, regexp)

Description

Renvoie un tableau contenant toutes les sous-chaînes de value qui correspondent à l'expression régulière (regexp).

La fonction REGEXP_EXTRACT_ALL ne renvoie que des correspondances qui ne se chevauchent pas. Par exemple, si vous utilisez cette fonction pour extraire ana de banana, la fonction ne renvoie qu'une sous-chaîne, pas deux.

Remarque : La bibliothèque re2 permet d'utiliser des expressions régulières dans Cloud Spanner SQL. Consultez la documentation de cette bibliothèque pour plus d'informations sur la syntaxe d'expression régulière à utiliser.

Type renvoyé

Un tableau de valeurs STRING ou BYTES

Exemples

WITH code_markdown AS
  (SELECT "Try `function(x)` or `function(y)`" as code)

SELECT
  REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;

+----------------------------+
| example                    |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+

REGEXP_REPLACE

REGEXP_REPLACE(value, regexp, replacement)

Description

Renvoie une STRING dans laquelle toutes les sous-chaînes de value qui correspondent à l'expression régulière regexp sont remplacées par l'argument replacement.

Vous pouvez utiliser des chiffres échappés par une barre oblique inverse (\1 à \9) dans l'argument replacement pour insérer du texte associé au groupe entre parenthèses correspondant dans le schéma regexp. Utilisez \0 pour faire référence à l'ensemble du texte associé.

Remarque : Pour ajouter une barre oblique inverse dans votre expression régulière, vous devez d'abord l'échapper. Par exemple, SELECT REGEXP_REPLACE("abc", "b(.)", "X\\1"); renvoie aXc.

La fonction REGEXP_REPLACE ne remplace que les correspondances qui ne se chevauchent pas. Par exemple, le fait de remplacer ana dans banana n'entraîne qu'un remplacement, pas deux.

Si l'argument regexp n'est pas une expression régulière valide, cette fonction renvoie une erreur.

Remarque : La bibliothèque re2 permet d'utiliser des expressions régulières dans Cloud Spanner SQL. Consultez la documentation de cette bibliothèque pour plus d'informations sur la syntaxe d'expression régulière à utiliser.

Type renvoyé

STRING ou BYTES

Exemples

WITH markdown AS
  (SELECT "# Heading" as heading
  UNION ALL
  SELECT "# Another heading" as heading)

SELECT
  REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
  AS html
FROM markdown;

+--------------------------+
| html                     |
+--------------------------+
| <h1>Heading</h1>         |
| <h1>Another heading</h1> |
+--------------------------+

REPLACE

REPLACE(original_value, from_value, to_value)

Description

Remplace toutes les occurrences de from_value par to_value dans original_value. Si from_value est vide, aucun remplacement n'est effectué.

Type renvoyé

STRING ou BYTES

Exemples

WITH desserts AS
  (SELECT "apple pie" as dessert
  UNION ALL
  SELECT "blackberry pie" as dessert
  UNION ALL
  SELECT "cherry pie" as dessert)

SELECT
  REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;

+--------------------+
| example            |
+--------------------+
| apple cobbler      |
| blackberry cobbler |
| cherry cobbler     |
+--------------------+

REPEAT

REPEAT(original_value, repetitions)

Description

Renvoie une valeur constituée de l'élément original_value, ainsi que d'une ou de plusieurs répétitions de celui-ci. Le paramètre repetitions indique le nombre de répétitions de la valeur original_value. Renvoie NULL si l'une des valeurs original_value ou repetitions est NULL.

Cette fonction renvoie une erreur si la valeur repetitions est négative.

Type renvoyé

STRING ou BYTES

Exemples

SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
  STRUCT('abc' AS t, 3 AS n),
  ('例子', 2),
  ('abc', null),
  (null, 3)
]);

t	n	REPEAT
abc	3	abcabcabc
例子	2	例子例子
abc	NULL	NULL
NULL	3	NULL

REVERSE

REVERSE(value)

Description

Renvoie l'inverse de la valeur STRING ou BYTES d'entrée.

Type renvoyé

STRING ou BYTES

Exemples

WITH example AS (
  SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
  SELECT "абвгд" AS sample_string, b"123" AS sample_bytes
)
SELECT
  sample_string,
  REVERSE(sample_string) AS reverse_string,
  sample_bytes,
  REVERSE(sample_bytes) AS reverse_bytes
FROM example;

+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo           | oof            | bar          | rab           |
| абвгд         | дгвба          | 123          | 321           |
+---------------+----------------+--------------+---------------+

RPAD

RPAD(original_value, return_length[, pattern])

Description

Renvoie une valeur composée de l'élément original_value et du suffixe pattern. Le paramètre return_length est une valeur INT64 qui indique la longueur de la valeur renvoyée. Si original_value est de type BYTES, return_length correspond au nombre d'octets. Si original_value est de type STRING, return_length correspond au nombre de caractères.

La valeur par défaut de pattern est une espace.

original_value et pattern doivent correspondre au même type de données.

Si return_length est inférieur ou égal à la longueur original_value, cette fonction renvoie la valeur original_value tronquée à la valeur de return_length. Par exemple, RPAD("hello world", 7); renvoie "hello w".

Si original_value, return_length ou pattern est NULL, cette fonction renvoie NULL.

Cette fonction renvoie une erreur si :

• return_length est une valeur négative ;
• pattern est vide.

Type renvoyé

STRING ou BYTES

Exemples

SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);

t	len	RPAD
abc	5	"abc  "
abc	2	"ab"
例子	4	"例子  "

SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);

t	len	pattern	RPAD
abc	8	def	"abcdefde"
abc	5	-	"abc--"
例子	5	中文	"例子中文中"

SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);

t	len	RPAD
b"abc"	5	b"abc  "
b"abc"	2	b"ab"
b"\xab\xcd\xef"	4	b"\xab\xcd\xef "

SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);

t	len	pattern	RPAD
b"abc"	8	b"def"	b"abcdefde"
b"abc"	5	b"-"	b"abc--"
b"\xab\xcd\xef"	5	b"\x00"	b"\xab\xcd\xef\x00\x00"

RTRIM

RTRIM(value1[, value2])

Description

Identique à TRIM, mais ne supprime que les caractères de fin.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  RTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| ***apple  |
| ***banana |
| ***orange |
+-----------+

WITH items AS
  (SELECT "applexxx" as item
  UNION ALL
  SELECT "bananayyy" as item
  UNION ALL
  SELECT "orangezzz" as item
  UNION ALL
  SELECT "pearxyz" as item)

SELECT
  RTRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

Description

Convertit une séquence d'octets en chaîne. Tous les caractères UTF-8 non valides sont remplacés par le caractère de remplacement Unicode U+FFFD.

Type renvoyé

STRING

Exemples

L'instruction suivante renvoie le caractère de remplacement Unicode (�).

SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;

SPLIT

SPLIT(value[, delimiter])

Description

Répartit value à l'aide de l'argument delimiter.

Pour STRING, le délimiteur par défaut est la virgule (,).

Pour BYTES, vous devez spécifier un délimiteur.

Une requête SPLIT faisant référence à un délimiteur vide produit un tableau de caractères UTF-8 pour les valeurs STRING et un tableau de BYTES pour les valeurs BYTES.

Une requête SPLIT sur une STRING vide renvoie un tableau contenant une seule STRING vide.

Type renvoyé

Tableau de type STRING ou tableau de type BYTES

Exemples

WITH letters AS
  (SELECT "" as letter_group
  UNION ALL
  SELECT "a" as letter_group
  UNION ALL
  SELECT "b c d" as letter_group)

SELECT SPLIT(letter_group, " ") as example
FROM letters;

+----------------------+
| example              |
+----------------------+
| []                   |
| [a]                  |
| [b, c, d]            |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

Description

Prend en considération deux valeurs. Renvoie TRUE si la deuxième valeur est un préfixe de la première.

Type renvoyé

BOOL

Exemples

WITH items AS
  (SELECT "foo" as item
  UNION ALL
  SELECT "bar" as item
  UNION ALL
  SELECT "baz" as item)

SELECT
  STARTS_WITH(item, "b") as example
FROM items;

+---------+
| example |
+---------+
|   False |
|    True |
|    True |
+---------+

STRPOS

STRPOS(string, substring)

Description

Renvoie l'index en base 1 de la première occurrence de substring dans string. Renvoie 0 si substring est introuvable.

Type renvoyé

INT64

Exemples

WITH email_addresses AS
  (SELECT
    "[e-mail]" AS email_address
  UNION ALL
  SELECT
    "[e-mail]" AS email_address
  UNION ALL
  SELECT
    "[e-mail]" AS email_address
  UNION ALL
  SELECT
    "quxexample.com" AS email_address)

SELECT
  STRPOS(email_address, "@") AS example
FROM email_addresses;

+---------+
| example |
+---------+
|       4 |
|       7 |
|      10 |
|       0 |
+---------+

SUBSTR

SUBSTR(value, position[, length])

Description

Renvoie une sous-chaîne de la valeur fournie. L'argument position est un entier spécifiant la position de départ de la sous-chaîne et "position = 1" indique le premier caractère ou octet. L'argument length correspond au nombre maximal de caractères pour les arguments STRING ou au nombre maximal d'octets pour les arguments BYTES.

Si la valeur de position est négative, la fonction effectue le comptage à partir de la fin de value et "-1" indique le dernier caractère.

Si position est une position située au-delà de l'extrémité gauche de la STRING (position = 0 ou position < -LENGTH(value)), la fonction commence le comptage à partir de "position = 1". Si length dépasse la longueur de value, la fonction renvoie un nombre de caractères inférieur à length.

Si la valeur length est inférieure à 0, la fonction renvoie une erreur.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2) as example
FROM items;

+---------+
| example |
+---------+
| pple    |
| anana   |
| range   |
+---------+

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, 2, 2) as example
FROM items;

+---------+
| example |
+---------+
| pp      |
| an      |
| ra      |
+---------+

WITH items AS
  (SELECT "apple" as item
  UNION ALL
  SELECT "banana" as item
  UNION ALL
  SELECT "orange" as item)

SELECT
  SUBSTR(item, -2) as example
FROM items;

+---------+
| example |
+---------+
| le      |
| na      |
| ge      |
+---------+

TO_BASE64

TO_BASE64(bytes_expr)

Description

Convertit une séquence de valeurs de type BYTES en une valeur de type STRING encodée en base64. Pour convertir une valeur de type STRING encodée en base64 en valeurs de type BYTES, utilisez FROM_BASE64.

Type renvoyé

STRING

Exemple

SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;

+---------------+
| base64_string |
+---------------+
| 3q2+7w==      |
+---------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

Description

Prend une valeur et renvoie un tableau de INT64.

• Si value est de type STRING, chaque élément du tableau renvoyé représente un point de code. Chaque point de code est compris dans les plages [0, 0xD7FF] et [0xE000, 0x10FFFF].
• Si value est de type BYTES, chaque élément du tableau est une valeur de caractère ASCII étendu comprise dans la plage [0, 255].

Pour découvrir comment convertir un tableau de points de code en STRING ou en BYTES, consultez les sections CODE_POINTS_TO_STRING ou CODE_POINTS_TO_BYTES.

Type renvoyé

Tableau (ARRAY) de valeurs INT64

Exemples

L'exemple suivant récupère les points de code pour chaque élément dans un tableau de mots.

SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;

+---------+------------------------------------+
| word    | code_points                        |
+---------+------------------------------------+
| foo     | [102, 111, 111]                    |
| bar     | [98, 97, 114]                      |
| baz     | [98, 97, 122]                      |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama   | [108, 108, 97, 109, 97]            |
+---------+------------------------------------+

L'exemple suivant convertit des représentations entières de BYTES en des valeurs de caractères ASCII correspondantes.

SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;

+------------------+------------------------+
| word             | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255]        |
| foo              | [102, 111, 111]        |
+------------------+------------------------+

L'exemple suivant montre la différence entre un résultat de type BYTES et un résultat de type STRING.

SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;

+------------+----------+
| b_result   | s_result |
+------------+----------+
| [196, 128] | [256]    |
+------------+----------+

Notez que le caractère Ā est représenté sous la forme d'une séquence Unicode de deux octets. Par conséquent, la version BYTES de TO_CODE_POINTS renvoie un tableau avec deux éléments, tandis que la version STRING renvoie un tableau contenant un seul élément.

TO_HEX

TO_HEX(bytes)

Description

Convertit une séquence de valeurs de type BYTES en une valeur de type STRING hexadécimale. Chaque octet de la STRING est transformé en deux caractères hexadécimaux compris dans la plage (0..9, a..f). Pour convertir une STRING encodée en hexadécimal en BYTES, utilisez FROM_HEX.

Type renvoyé

STRING

Exemple

WITH Input AS (
  SELECT b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF' AS byte_str UNION ALL
  SELECT b'foobar'
)
SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM Input;

+----------------------------------+------------------+
| byte_string                      | hex_string       |
+----------------------------------+------------------+
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
| foobar                           | 666f6f626172     |
+----------------------------------+------------------+

TRIM

TRIM(value1[, value2])

Description

Supprime tous les caractères de début et de fin qui correspondent à value2. Si value2 n'est pas spécifiée, tous les espaces blancs de début et de fin (tels que définis par la norme Unicode) sont supprimés. Si le premier argument est de type BYTES, le deuxième argument est obligatoire.

Si value2 contient plusieurs caractères ou octets, la fonction supprime tous les caractères ou octets de début et de fin contenus dans value2.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT "   apple   " as item
  UNION ALL
  SELECT "   banana   " as item
  UNION ALL
  SELECT "   orange   " as item)

SELECT
  CONCAT("#", TRIM(item), "#") as example
FROM items;

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+

WITH items AS
  (SELECT "***apple***" as item
  UNION ALL
  SELECT "***banana***" as item
  UNION ALL
  SELECT "***orange***" as item)

SELECT
  TRIM(item, "*") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
+---------+

WITH items AS
  (SELECT "xxxapplexxx" as item
  UNION ALL
  SELECT "yyybananayyy" as item
  UNION ALL
  SELECT "zzzorangezzz" as item
  UNION ALL
  SELECT "xyzpearxyz" as item)

SELECT
  TRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

UPPER

UPPER(value)

Description

Pour les arguments STRING, cette fonction renvoie la chaîne d'origine avec tous les caractères alphabétiques en majuscules. Le mappage entre majuscules et minuscules est effectué en fonction de la base de données de caractères Unicode, sans prise en compte des mappages spécifiques à la langue.

Les arguments BYTES sont quant à eux traités comme du texte ASCII et les octets supérieurs à 127 ne sont pas modifiés.

Type renvoyé

STRING ou BYTES

Exemples

WITH items AS
  (SELECT
    "foo" as item
  UNION ALL
  SELECT
    "bar" as item
  UNION ALL
  SELECT
    "baz" as item)

SELECT
  UPPER(item) AS example
FROM items;

+---------+
| example |
+---------+
| FOO     |
| BAR     |
| BAZ     |
+---------+