Catégories :: Fonctions de chaîne et fonctions binaires (Correspondance/Comparaison)

SUBSTR , SUBSTRING¶

Renvoie la partie de la chaîne ou de la valeur binaire de base_expr, à partir du caractère/octet spécifié par start_expr, avec une longueur éventuellement limitée.

Ces fonctions sont synonymes.

Voir aussi ::: LEFT, RIGHT

Syntaxe¶

SUBSTR( <base_expr>, <start_expr> [ , <length_expr> ] )

SUBSTRING( <base_expr>, <start_expr> [ , <length_expr> ] )

Copy

Arguments¶

base_expr

Une expression qui est évaluée sur une valeur VARCHAR ou BINARY.

start_expr

Une expression qui donne un entier. Elle spécifie le décalage à partir duquel la sous-chaîne commence. Le décalage est mesuré ainsi :

Le nombre de caractères UTF-8 si l’entrée est une valeur VARCHAR.
Le nombre d’octets si l’entrée est une valeur BINARY.

La position de début est basée sur 1, pas sur 0. Par exemple, SUBSTR('abc', 1, 1) renvoie a, pas b.

length_expr

Une expression qui donne un entier. Elle spécifie :

Le nombre de caractères UTF-8 à renvoyer si l’entrée est VARCHAR.
Le nombre d’octets à renvoyer si l’entrée est BINARY.

Spécifiez une longueur supérieure ou égale à zéro. Si la longueur est un nombre négatif, la fonction renvoie une chaîne vide.

Renvoie¶

Le type de données de la valeur renvoyée est identique à celui du type de données de base_expr (VARCHAR ou BINARY).

Si l’une des entrées est NULL, NULL est renvoyé.

Notes sur l’utilisation¶

Si length_expr est spécifié, jusqu’à length_expr des caractères/octets sont renvoyés. Si length_expr n’est pas spécifié, tous les caractères jusqu’à la fin de la chaîne ou de la valeur binaire sont renvoyés.
Les valeurs dans start_expr commencent à 1 :
- Si un 0 est spécifié, il est traité comme 1.
- Si une valeur négative est spécifiée, la position de départ est calculée en tant que start_expr caractères/octets à partir de la fin de la chaîne ou de la valeur binaire. Si la position est en dehors de la plage d’une chaîne ou d’une valeur binaire, une valeur vide est renvoyée.

Détails du classement¶

Le classement s’applique aux entrées VARCHAR. Le classement ne s’applique pas si le type de données en entrée du premier paramètre est BINARY.
No impact. Bien que le classement soit accepté syntaxiquement, les classements n’affectent pas le traitement. Par exemple, les lettres à deux et trois caractères dans les langues (par exemple, « dzs » en hongrois ou « ch » en tchèque) sont toujours comptées comme deux ou trois caractères (et non comme un seul caractère) pour l’argument de longueur.
The collation of the result is the same as the collation of the input. Cela peut être utile si la valeur renvoyée est transmise à une autre fonction dans le cadre d’appels de fonctions imbriquées.

Exemples¶

Les exemples suivants utilisent la fonction SUBSTR.

Exemple de base¶

L’exemple suivant utilise la fonction SUBSTR permettant de renvoyer la partie de la chaîne qui commence au neuvième caractère et limite la longueur de la valeur renvoyée à trois caractères :

SELECT SUBSTR('testing 1 2 3', 9, 3);

Copy

+-------------------------------+
| SUBSTR('TESTING 1 2 3', 9, 3) |
|-------------------------------|
| 1 2                           |
+-------------------------------+

Spécification de différentes valeurs de début et de longueur¶

L’exemple suivant montre les sous-chaînes renvoyées pour le même base_expr lorsque des valeurs différentes sont spécifiées pour start_expr et length_expr :

CREATE OR REPLACE TABLE test_substr (
    base_value VARCHAR,
    start_value INT,
    length_value INT)
  AS SELECT
    column1,
    column2,
    column3
  FROM
    VALUES
      ('mystring', -1, 3),
      ('mystring', -3, 3),
      ('mystring', -3, 7),
      ('mystring', -5, 3),
      ('mystring', -7, 3),
      ('mystring', 0, 3),
      ('mystring', 0, 7),
      ('mystring', 1, 3),
      ('mystring', 1, 7),
      ('mystring', 3, 3),
      ('mystring', 3, 7),
      ('mystring', 5, 3),
      ('mystring', 5, 7),
      ('mystring', 7, 3),
      ('mystring', NULL, 3),
      ('mystring', 3, NULL);

SELECT base_value,
       start_value,
       length_value,
       SUBSTR(base_value, start_value, length_value) AS substring
  FROM test_substr;

Copy

+------------+-------------+--------------+-----------+
| BASE_VALUE | START_VALUE | LENGTH_VALUE | SUBSTRING |
|------------+-------------+--------------+-----------|
| mystring   |          -1 |            3 | g         |
| mystring   |          -3 |            3 | ing       |
| mystring   |          -3 |            7 | ing       |
| mystring   |          -5 |            3 | tri       |
| mystring   |          -7 |            3 | yst       |
| mystring   |           0 |            3 | mys       |
| mystring   |           0 |            7 | mystrin   |
| mystring   |           1 |            3 | mys       |
| mystring   |           1 |            7 | mystrin   |
| mystring   |           3 |            3 | str       |
| mystring   |           3 |            7 | string    |
| mystring   |           5 |            3 | rin       |
| mystring   |           5 |            7 | ring      |
| mystring   |           7 |            3 | ng        |
| mystring   |        NULL |            3 | NULL      |
| mystring   |           3 |         NULL | NULL      |
+------------+-------------+--------------+-----------+

Renvoyer des sous-chaînes pour les chaînes d’e-mail, de téléphone et de date¶

Les exemples suivants renvoient des sous-chaînes d’informations clientes dans une table.

Créez la table et insérez les données :

CREATE OR REPLACE TABLE customer_contact_example (
    cust_id INT,
    cust_email VARCHAR,
    cust_phone VARCHAR,
    activation_date VARCHAR)
  AS SELECT
    column1,
    column2,
    column3,
    column4
  FROM
    VALUES
      (1, 'some_text@example.com', '800-555-0100', '20210320'),
      (2, 'some_other_text@example.org', '800-555-0101', '20240509'),
      (3, 'some_different_text@example.net', '800-555-0102', '20191017');

SELECT * from customer_contact_example;

Copy

+---------+---------------------------------+--------------+-----------------+
| CUST_ID | CUST_EMAIL                      | CUST_PHONE   | ACTIVATION_DATE |
|---------+---------------------------------+--------------+-----------------|
|       1 | some_text@example.com           | 800-555-0100 | 20210320        |
|       2 | some_other_text@example.org     | 800-555-0101 | 20240509        |
|       3 | some_different_text@example.net | 800-555-0102 | 20191017        |
+---------+---------------------------------+--------------+-----------------+

Utilisez la fonction POSITION avec la fonction SUBSTR pour extraire les domaines à partir des adresses e-mail. Cet exemple trouve la position de @ dans chaque chaîne et commence à partir du caractère suivant en ajoutant un :

SELECT cust_id,
       cust_email,
       SUBSTR(cust_email, POSITION('@' IN cust_email) + 1) AS domain
  FROM customer_contact_example;

Copy

+---------+---------------------------------+-------------+
| CUST_ID | CUST_EMAIL                      | DOMAIN      |
|---------+---------------------------------+-------------|
|       1 | some_text@example.com           | example.com |
|       2 | some_other_text@example.org     | example.org |
|       3 | some_different_text@example.net | example.net |
+---------+---------------------------------+-------------+

Astuce

Vous pouvez utiliser la fonction POSITION permettant de trouver la position d’autres caractères, comme un caractère vide (' ') ou un trait de soulignement (_).

Dans la colonne cust_phone de la table, l’indicatif régional correspond toujours aux trois premiers caractères. Extrayez l’indicatif régional des numéros de téléphone :

SELECT cust_id,
       cust_phone,
       SUBSTR(cust_phone, 1, 3) AS area_code
  FROM customer_contact_example;

Copy

+---------+--------------+-----------+
| CUST_ID | CUST_PHONE   | AREA_CODE |
|---------+--------------+-----------|
|       1 | 800-555-0100 | 800       |
|       2 | 800-555-0101 | 800       |
|       3 | 800-555-0102 | 800       |
+---------+--------------+-----------+

Supprimer l’indicatif régional des numéros de téléphone :

SELECT cust_id,
       cust_phone,
       SUBSTR(cust_phone, 5) AS phone_without_area_code
  FROM customer_contact_example;

Copy

+---------+--------------+-------------------------+
| CUST_ID | CUST_PHONE   | PHONE_WITHOUT_AREA_CODE |
|---------+--------------+-------------------------|
|       1 | 800-555-0100 | 555-0100                |
|       2 | 800-555-0101 | 555-0101                |
|       3 | 800-555-0102 | 555-0102                |
+---------+--------------+-------------------------+

Dans la colonne activation_date de la table, la date est toujours au format YYYYMMDD. Extraire l’année, le mois et le jour de ces chaînes :

SELECT cust_id,
       activation_date,
       SUBSTR(activation_date, 1, 4) AS year,
       SUBSTR(activation_date, 5, 2) AS month,
       SUBSTR(activation_date, 7, 2) AS day
  FROM customer_contact_example;

Copy

+---------+-----------------+------+-------+-----+
| CUST_ID | ACTIVATION_DATE | YEAR | MONTH | DAY |
|---------+-----------------+------+-------+-----|
|       1 | 20210320        | 2021 | 03    | 20  |
|       2 | 20240509        | 2024 | 05    | 09  |
|       3 | 20191017        | 2019 | 10    | 17  |
+---------+-----------------+------+-------+-----+