Funciones de texto

En resumen: En SQLite, las funciones de texto unen texto con ||, lo miden con LENGTH, lo cortan con SUBSTR, encuentran una posición con INSTR y lo limpian con REPLACE y TRIM.

Problema

Las etiquetas de los estantes

La tienda imprime etiquetas para los estantes: el nombre del producto y el precio, como «Pan blanco (1,20 €)». El nombre está en una columna y el precio en otra. Necesitas una columna con el texto terminado, para mandarla directamente a la impresora.

La idea

Unir con ||

|| une texto. Los trozos pueden ser columnas, texto entre comillas o números:

SQLbase de datos: tienda

SELECT name || ' (' || price || ' €)' AS label
FROM products
WHERE category_id = 3;

SELECT name || ' (' || price || ' €)' AS label FROM products WHERE category_id = 3;

▶ EjecutarRestaurar la consultaBase nueva

Casi. El número se convierte en texto solo, pero en su forma más corta: 1.2, con punto y sin el cero. Para una etiqueta hacen falta dos dígitos y una coma.

printf('%.2f', price) escribe el número con exactamente dos dígitos después del punto: 1.20. REPLACE(texto, qué, por qué) cambia el punto por una coma:

SQLbase de datos: tienda

SELECT name || ' (' || REPLACE(printf('%.2f', price), '.', ',') || ' €)' AS label
FROM products
WHERE category_id = 3;

SELECT name || ' (' || REPLACE(printf('%.2f', price), '.', ',') || ' €)' AS label FROM products WHERE category_id = 3;

▶ EjecutarRestaurar la consultaBase nueva

Las funciones se anidan: REPLACE recibe el texto ya hecho de printf.

Más precisamente, para curiosos

printf es una función de SQLite. Desde la versión 3.38 también se llama format. En PostgreSQL para lo mismo existe to_char(price, 'FM990.00'), y en MySQL FORMAT(price, 2). Si la página sale en varios idiomas, es mejor que el número siga siendo un número y que el programa que muestra la página le dé el formato.

Pruébalo

El destinatario del correo

Los programas de correo muestran una dirección como «Maria Ivanova <maria.ivanova@example.com>». Haz una columna así para cada cliente y llámala recipient.

SQLbase de datos: tienda

SELECT name, email FROM customers;

SELECT name, email FROM customers;

Comprobar▶ Ejecutar

Míralo

Falta una ciudad

El mensajero quiere una línea «nombre, ciudad» para cada cliente:

SQLbase de datos: tienda

SELECT name, city, name || ', ' || city AS contact
FROM customers
WHERE id BETWEEN 10 AND 13;

SELECT name, city, name || ', ' || city AS contact FROM customers WHERE id BETWEEN 10 AND 13;

▶ EjecutarRestaurar la consultaBase nueva

1. ¿Por qué la línea de contacto de Boris Mitev es NULL?

Porque su ciudad es NULL, y el texto unido con NULL también es NULLPorque su nombre es demasiado largoPorque || solo funciona con números

Comprobar

Cuando una columna puede ser NULL, el texto unido también puede serlo. Cómo poner palabras en lugar de la ciudad que falta llega en la lección sobre CASE y COALESCE.

La idea

Partes del texto

Las funciones de texto que más usarás:

• LENGTH(t): cuántos caracteres tiene.
• SUBSTR(t, desde, cantidad): un trozo del texto. La cuenta empieza en 1.
• INSTR(t, parte): la posición de la parte, o 0 si no está.
• UPPER(t) y LOWER(t): mayúsculas y minúsculas.
• REPLACE(t, qué, por qué) y TRIM(t), que quita los espacios del principio y del final.

SQLbase de datos: tienda

SELECT name, LENGTH(name), SUBSTR(name, 1, 4), INSTR(name, ' ')
FROM products
WHERE category_id = 4;

SELECT name, LENGTH(name), SUBSTR(name, 1, 4), INSTR(name, ' ') FROM products WHERE category_id = 4;

▶ EjecutarRestaurar la consultaBase nueva

SUBSTR e INSTR juntos cortan el texto justo en el sitio correcto. El dominio de un correo es todo lo que va después de la @:

SQLbase de datos: tienda

SELECT email,
       INSTR(email, '@') AS at,
       SUBSTR(email, INSTR(email, '@') + 1) AS domain
FROM customers
WHERE city = 'Varna';

SELECT email, INSTR(email, '@') AS at, SUBSTR(email, INSTR(email, '@') + 1) AS domain FROM customers WHERE city = 'Varna';

▶ EjecutarRestaurar la consultaBase nueva

TRIM hace falta cuando los datos vienen de un formulario o de una hoja de cálculo y alguien dejó un espacio:

SQLbase de datos: tienda

SELECT LENGTH('  Pan blanco '), LENGTH(TRIM('  Pan blanco '));

SELECT LENGTH(' Pan blanco '), LENGTH(TRIM(' Pan blanco '));

▶ EjecutarRestaurar la consultaBase nueva

Pruébalo

Iniciales

Las tarjetas de fidelidad necesitan las iniciales del cliente: «M. I.» para Maria Ivanova. Muestra el nombre y las iniciales en una columna llamada initials.

SQLbase de datos: tienda

SELECT name, SUBSTR(name, 1, 1) FROM customers;

SELECT name, SUBSTR(name, 1, 1) FROM customers;

Comprobar▶ Ejecutar

Míralo

UPPER y las letras acentuadas

El sitio web quiere una dirección para cada producto, como pan-blanco: minúsculas y un guion en lugar del espacio. REPLACE y LOWER lo hacen:

SQLbase de datos: tienda

SELECT name, LOWER(REPLACE(name, ' ', '-')) AS slug
FROM products
WHERE category_id = 3;

SELECT name, LOWER(REPLACE(name, ' ', '-')) AS slug FROM products WHERE category_id = 3;

▶ EjecutarRestaurar la consultaBase nueva

Aquí funciona porque los nombres solo usan las letras de la A a la Z. Los UPPER y LOWER integrados de SQLite cambian solo esas letras latinas sencillas. Una letra con tilde, o un alfabeto entero distinto como el cirílico o el griego, se queda como está:

SQLbase de datos: tienda

SELECT UPPER('cafe'), UPPER('café');

SELECT UPPER('cafe'), UPPER('café');

▶ EjecutarRestaurar la consultaBase nueva

No hay error, así que es fácil pasarlo por alto. Lo mismo vale para LIKE: ignora las mayúsculas solo en las letras de la A a la Z.

Más precisamente, para curiosos

PostgreSQL y MySQL con texto en UTF-8 cambian correctamente también las letras acentuadas y otros alfabetos. SQLite tiene una extensión (ICU) que enseña a UPPER, LOWER y LIKE todos los alfabetos, pero aquí no está disponible en el navegador. Por eso los programas que usan SQLite suelen guardar el texto de búsqueda en una columna aparte, ya en minúsculas, calculado por el programa.

Ejercicio

Dos tareas

1. En una etiqueta caben 18 caracteres. ¿Qué productos tienen un nombre más largo? Muestra el nombre y su longitud en una columna llamada length.

Los productos con un nombre de más de 18 caracteres:

SQLbase de datos: tienda

Comprobar▶ Ejecutar

2. El nuevo sistema de acceso necesita un nombre de usuario: la parte del correo antes de la @. Muestra el correo y el nombre de usuario en una columna llamada username.

El correo y el nombre de usuario de cada cliente:

SQLbase de datos: tienda

SELECT email, INSTR(email, '@') FROM customers;

SELECT email, INSTR(email, '@') FROM customers;

Comprobar▶ Ejecutar

Test

Ponte a prueba

1. ¿Qué da 'Pan' || ' ' || 'blanco'?

PanblancoUn error: || significa «o»Pan blanco

2. ¿Qué devuelve SUBSTR('Kashkaval', 1, 4)?

avalashkKash

3. ¿Qué devuelve INSTR('Yogur', ' ')?

0NULL-1

4. ¿Qué da UPPER('café') en SQLite?

CAFÉUn errorCAFé

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: WHERE: filtrar filas · IN, BETWEEN y LIKE · NULL: sin valor · Aritmética en una consulta
• Continúa en: CASE y los valores que faltan · Árboles y secuencias: WITH RECURSIVE · Consultas lentas · Inyección SQL y parámetros
• Ejercicios sobre el tema: Expresiones y funciones