Reformateo de datos de texto y caracteres

Funciones para manipular datos en PostgreSQL

Brian Piccolo

Sr. Director, Digital Strategy

Temas

Reformatear datos de texto y caracteres.
Analizar datos de texto y caracteres.
Determinar longitud y posición de caracteres.
Truncar y rellenar texto.

El operador de concatenación

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

Concatenación con funciones

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

Concatenar con entrada no textual

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

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

Cambiar mayúsculas/minúsculas en texto

SELECT 
  UPPER(email) 
FROM customer;

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

Cambiar mayúsculas/minúsculas en texto

SELECT 
  LOWER(title) 
FROM film;

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

Cambiar mayúsculas/minúsculas en texto

SELECT 
  INITCAP(title) 
FROM film;

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

Reemplazar caracteres en un texto

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

Reemplazar caracteres en un texto

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

Manipular texto con REVERSE

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

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

¡Vamos a practicar!

Funciones para manipular datos en PostgreSQL