Analyser des données de type chaîne et caractère
Fonctions pour manipuler les données dans PostgreSQL
Brian Piccolo
Sr. Director, Digital Strategy
Déterminer 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éterminer la longueur d'une chaîne
SELECT
title,
LENGTH(title)
FROM film;
+-------------------+----------------+
| title | LENGTH(title) |
|-------------------|----------------|
| ACADEMY DINOSAUR | 16 |
| ACE GOLDFINGER | 14 |
| ADAPTATION HOLES | 16 |
+-------------------+----------------+
Repérer 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 |
+-------------------------------------+------------------------+
Repérer 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 |
+-------------------------------------+--------------------+
Analyser des données textuelles
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 |
+----------------------------------------------------+
Analyser des données textuelles
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 |
+----------------------------------------------------+
Extraire des sous-chaînes 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 |
+----------------------------------------------------+
Extraire des sous-chaînes 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 |
+----------------------------------------------------+
Extraire des sous-chaînes 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 |
+-----------------------------------------------------------------------+
Extraire des sous-chaînes 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 pour manipuler les données dans PostgreSQL
