Zeichenketten und Zeichendaten umformatieren

Funktionen zur Datenbearbeitung in PostgreSQL

Brian Piccolo

Sr. Director, Digital Strategy

Themen

Zeichenketten und Zeichendaten umformatieren
Zeichenketten und Zeichendaten analysieren.
Länge von Zeichenketten und Position der Zeichen bestimmen
Zeichenkettendaten abschneiden und auffüllen

Der Zeichenfolgenverkettungsoperator

SELECT 
  first_name,
  last_name,
  first_name || ' ' || last_name AS full_name
FROM customer

+------------+-----------+-------------------+
| first_name | last_name | full_name         |
|------------|-----------|-------------------|
| MARY       | SMITH     | MARY SMITH        |
| LINDA      | WILLIAMS  | LINDA WILLIAMS    |
+------------+-----------+-------------------+

Zeichenfolgenverkettung mit Funktionen

SELECT 
  CONCAT(first_name,' ', last_name) AS full_name
FROM customer;

+--------------------------------------------+
| first_name | last_name | full_name         |
|--------------------------------------------|
| MARY       | SMITH     | MARY SMITH        |
| LINDA      | WILLIAMS  | LINDA WILLIAMS    |
+--------------------------------------------+

Zeichenfolgenverkettung mit einer Eingabe ohne Zeichenketten

SELECT 
  customer_id || ': ' 
  || first_name || ' ' 
  || last_name AS full_name
FROM customer;

+-------------------+
| full_name         |
|-------------------|
| 1: MARY SMITH     |
| 2: LINDA WILLIAMS |
+-------------------+

Groß-/Kleinschreibung von Zeichenfolgen ändern

SELECT 
  UPPER(email) 
FROM customer;

+-------------------------------------+
| UPPER(email)                        |
|-------------------------------------|
| [email protected]       |
| [email protected] |
| [email protected]   |
+-------------------------------------+

Groß-/Kleinschreibung von Zeichenfolgen ändern

SELECT 
  LOWER(title) 
FROM film;

+-------------------+
| LOWER(title)      |
|-------------------|
| academy dinosaur  | 
| ace goldfinger    |
| adaptation holes  |
+-------------------+

Groß-/Kleinschreibung von Zeichenfolgen ändern

SELECT 
  INITCAP(title) 
FROM film;

+-------------------+
| INITCAP(title)    |
|-------------------|
| Academy Dinosaur  | 
| Ace Goldfinger    |
| Adaptation Holes  |
+-------------------+

Zeichen in einer Zeichenfolge ersetzen

SELECT description FROM film;

+---------------------------------------------------------+
| description                                             |
|---------------------------------------------------------|
| A Epic Drama of a Feminist And a Mad Scientist...       |
| A Astounding Epistle of a Database Administrator...     |
| A Astounding Reflection of a Lumberjack And a Car...    |
| A Fanciful Documentary of a Frisbee And a Lumberjack... |
| A Fast-Paced Documentary of a Pastry Chef And a...      |
+---------------------------------------------------------+

Zeichen in einer Zeichenfolge ersetzen

SELECT 
    REPLACE(description, 'A Astounding', 
              'An Astounding') as description 
FROM film;

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

Zeichenketten mit REVERSE bearbeiten

SELECT 
  title, 
  REVERSE(title) 
FROM 
  film AS f;

+-------------------------------------+
| title            | reverse(title)   |
|-------------------------------------|
| ACADEMY DINOSAUR | RUASONID YMEDACA |
| ACE GOLDFINGER   | REGNIFDLOG ECA   |
+-------------------------------------+

Lass uns üben!

Funktionen zur Datenbearbeitung in PostgreSQL