Zeichenketten und Zeichendaten analysieren

Funktionen zur Datenbearbeitung in PostgreSQL

Brian Piccolo

Sr. Director, Digital Strategy

Die Länge einer Zeichenkette bestimmen

SELECT 
    title,
    CHAR_LENGTH(title)
FROM film;

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

Die Länge einer Zeichenkette bestimmen

SELECT
    title,
    LENGTH(title)
FROM film;

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

Die Position eines Zeichens in einer Zeichenkette finden

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

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

Die Position eines Zeichens in einer Zeichenkette finden

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

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

Zeichenkettendaten analysieren

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  |
+----------------------------------------------------+

Zeichenkettendaten analysieren

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 |
+----------------------------------------------------+

Teilzeichenketten aus Zeichendaten extrahieren

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  |
+----------------------------------------------------+

Teilzeichenketten aus Zeichendaten extrahieren

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                                     |
+----------------------------------------------------+

Teilzeichenketten aus Zeichendaten extrahieren

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                                                    |
+-----------------------------------------------------------------------+

Teilzeichenketten aus Zeichendaten extrahieren

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  |
+----------------------------------------------------+

Lass uns üben!

Funktionen zur Datenbearbeitung in PostgreSQL