Analyse des données de type chaîne et caractère

Fonctions permettant de manipuler les données dans PostgreSQL

Brian Piccolo

Sr. Director, Digital Strategy

Détermination de la longueur d’une chaîne

SELECT 
    title,
    CHAR_LENGTH(title)
FROM film;

+-------------------+---------------------+
| title             | CHAR_LENGTH(title)  |
|-------------------+---------------------|
| ACADEMY DINOSAUR  | 16                  |
| ACE GOLDFINGER    | 14                  |
| ADAPTATION HOLES  | 16                  |
+-------------------+---------------------+

Détermination de la longueur d’une chaîne

SELECT
    title,
    LENGTH(title)
FROM film;

+-------------------+----------------+
| title             | LENGTH(title)  |
|-------------------+----------------|
| ACADEMY DINOSAUR  | 16             |
| ACE GOLDFINGER    | 14             |
| ADAPTATION HOLES  | 16             |
+-------------------+----------------+

Identification de la position d'un caractère dans une chaîne

SELECT
    email,
    POSITION('@' IN email)
FROM customer;

+-------------------------------------+------------------------+
| email                               | POSITION('@' IN email) |
|-------------------------------------|------------------------|
| [email protected]       | 11                     |
| [email protected] | 17                     |
| [email protected]   | 15                     |
+-------------------------------------+------------------------+

Identification de la position d'un caractère dans une chaîne

SELECT
    email,
    STRPOS(email, '@')
FROM customer;

+-------------------------------------+--------------------+
| email                               | STRPOS(email, '@') |
|-------------------------------------|--------------------|
| [email protected]       | 11                 |
| [email protected] | 17                 |
| [email protected]   | 15                 |
+-------------------------------------+--------------------+

Analyse des données de chaîne

SELECT
    LEFT(description, 50)
FROM film;

+----------------------------------------------------+
| description                                        |
|----------------------------------------------------|
| A Epic Drama of a Feminist And a Mad Scientist who |
| A Astounding Epistle of a Database Administrator A |
| A Astounding Reflection of a Lumberjack And a Car  |
+----------------------------------------------------+

Analyse des données de chaîne

SELECT
    RIGHT(description, 50)
FROM film;

+----------------------------------------------------+
| description                                        |
|----------------------------------------------------|
|  who must Battle a Teacher in The Canadian Rockies |
| nd a Explorer who must Find a Car in Ancient China |
| Car who must Sink a Lumberjack in A Baloon Factory |
+----------------------------------------------------+

Extraction de sous-chaînes de données de caractères

SELECT
    SUBSTRING(description, 10, 50)
FROM
  film AS f;

+----------------------------------------------------+
| description                                        |
|----------------------------------------------------|
| ama of a Feminist And a Mad Scientist who must Bat |
| ing Epistle of a Database Administrator And a Expl |
| ing Reflection of a Lumberjack And a Car who must  |
+----------------------------------------------------+

Extraction de sous-chaînes de données de caractères

SELECT
    SUBSTRING(email FROM 0 FOR POSITION('@' IN email))
FROM
  customer;

+----------------------------------------------------+
| SUBSTRING(email FROM 0 FOR POSITION('@' IN email)) |
|----------------------------------------------------|
| MARY.SMITH                                         |
| PATRICIA.JOHNSON                                   |
| LINDA.WILLIAMS                                     |
+----------------------------------------------------+

Extraction de sous-chaînes de données de caractères

SELECT 
    SUBSTRING(email FROM POSITION('@' IN email)+1 FOR CHAR_LENGTH(email))
FROM 
  customer;

+-----------------------------------------------------------------------+
| SUBSTRING(email FROM POSITION('@' IN email)+1 FOR CHAR_LENGTH(email)) |
|-----------------------------------------------------------------------|
| sakilacustomer.org                                                    |
| sakilacustomer.org                                                    |
| sakilacustomer.org                                                    |
+-----------------------------------------------------------------------+

Extraction de sous-chaînes de données de caractères

SELECT
    SUBSTR(description, 10, 50)
FROM
  film AS f;

+----------------------------------------------------+
| description                                        |
|----------------------------------------------------|
| ama of a Feminist And a Mad Scientist who must Bat |
| ing Epistle of a Database Administrator And a Expl |
| ing Reflection of a Lumberjack And a Car who must  |
+----------------------------------------------------+

Passons à la pratique !

Fonctions permettant de manipuler les données dans PostgreSQL