Funciones de cadenas en SQLite: SUBSTR, REPLACE, INSTR

Las cadenas son donde viven las consultas reales

Con los números es fácil. Lo complicado llega con las cadenas: nombres con espacios sobrantes, correos en mayúsculas y minúsculas mezcladas, identificadores pegados con guiones, campos de texto libre que casi —pero no del todo— coinciden. SQLite trae un conjunto pequeño y bien pensado de funciones de cadenas que resuelve la mayoría de estos casos sin tener que recurrir a código de aplicación.

En esta página repasamos las que más vas a usar: concatenar, recortar, buscar, reemplazar, limpiar y formatear.

Concatenar cadenas en SQLite con el operador ||

SQLite no tiene una función CONCAT. Para unir cadenas se usa el operador ||:

SELECT 'Hola, ' || '¡mundo!' AS greeting;

SELECT 'Usuario n.º ' || 42 || ' se ha unido' AS message;

Los números y otros tipos se convierten a texto de forma automática. El detalle a tener en cuenta: si cualquiera de los operandos es NULL, toda la expresión se vuelve NULL. Es el comportamiento estándar de SQL, pero suele pillar desprevenido a más de uno:

SELECT 'Hola, ' || NULL || '!' AS greeting;

SELECT 'Hola, ' || COALESCE(NULL, 'amigo') || '!' AS safe_greeting;

Envuelve las columnas que admiten NULL con COALESCE(col, '') o COALESCE(col, 'valor_por_defecto') cuando no quieras que un valor ausente te reviente toda la cadena.

LENGTH, UPPER y LOWER

Estas tres las vas a usar todo el tiempo:

SELECT
    LENGTH('currículum')    AS char_count,
    UPPER('hola mundo')     AS upper_case,
    LOWER('HOLA MUNDO')     AS lower_case;

LENGTH devuelve la cantidad de caracteres del texto, no los bytes. Si lo que quieres son bytes (un caso poco común, pero útil para analizar almacenamiento), usa OCTET_LENGTH. Por defecto, UPPER y LOWER solo transforman letras ASCII: los caracteres acentuados pasan tal cual, salvo que tengas cargada la extensión ICU.

SUBSTR en SQLite: extraer parte de una cadena

SUBSTR(text, start, length) extrae un fragmento de una cadena. Los índices empiezan en 1, no en 0 — es decir, 1 corresponde al primer carácter:

SELECT
    SUBSTR('codecademy', 1, 4)  AS primeros_cuatro,
    SUBSTR('codecademy', 5)     AS resto,
    SUBSTR('codecademy', -3)    AS ultimos_tres;

Algunas cosas que conviene tener presentes:

• El tercer argumento es opcional. Si lo omites, obtienes todo desde start hasta el final de la cadena.
• Un start negativo cuenta desde el final.
• Si start se pasa del final, te devuelve una cadena vacía, no un error.

SUBSTRING también se acepta como sinónimo, por si vienes con la memoria muscular de otra base de datos.

INSTR en SQLite: localizar una subcadena

INSTR(haystack, needle) devuelve la posición (empezando en 1) de la primera aparición de needle dentro de haystack, o 0 si no la encuentra:

SELECT
    INSTR('user@example.com', '@')        AS at_position,
    INSTR('user@example.com', 'xyz')      AS not_found,
    SUBSTR('user@example.com',
           1,
           INSTR('user@example.com', '@') - 1) AS local_part;

Esa última expresión es el modismo clásico de SQLite para «todo lo que va antes de @»: localizas el delimitador con INSTR y cortas con SUBSTR. Vas a escribir esta combinación un montón de veces. Ojo con un detalle: cuando INSTR no encuentra nada devuelve 0, así que conviene comprobarlo antes de cortar — si le pasas 0 a SUBSTR, te devuelve resultados raros sin avisar.

REPLACE: reemplazar una subcadena por otra en SQLite

REPLACE(text, old, new) sustituye todas las apariciones de old por new:

SELECT
    REPLACE('555-123-4567', '-', '')                AS digits_only,
    REPLACE('Hola, mundo. Hola, de nuevo.',
            'Hola',
            'Qué tal')                              AS shortened,
    REPLACE('  espaciado  texto  ', ' ', '')        AS no_spaces;

Distingue entre mayúsculas y minúsculas, y no acepta expresiones regulares: solo busca subcadenas literales. Para transformaciones más complejas puedes encadenar varias llamadas a REPLACE, pero a partir de dos o tres anidadas conviene mover esa lógica a la aplicación.

TRIM, LTRIM y RTRIM en sqlite

Los datos que introduce el usuario suelen llegar con espacios sobrantes al principio o al final. TRIM se encarga de limpiarlos:

SELECT
    TRIM('   hola   ')               AS ambos_lados,
    LTRIM('   hola   ')              AS solo_izquierda,
    RTRIM('   hola   ')              AS solo_derecha,
    TRIM('---hola---', '-')          AS caracteres_personalizados;

De forma predeterminada, eliminan espacios. Si pasas un segundo argumento, puedes indicar qué caracteres quitar: ojo, cada carácter de ese segundo argumento se interpreta como parte de un "conjunto de caracteres a eliminar", no como una subcadena literal. Por eso TRIM('xxxhelloxx', 'x') devuelve 'hola'.

printf en SQLite: formatear números y cadenas

Cuando necesitas dar formato a una cadena —decimales fijos, números con relleno, salida en hexadecimal—, la función printf (también disponible como format) es la indicada:

SELECT
    printf('%.2f',         3.14159)            AS two_decimals,
    printf('%05d',         42)                 AS padded,
    printf('$%,.2f',       1234567.89)         AS money,
    printf('%s tiene %d años',     'Ada',  30)         AS sentence;

Los especificadores de formato siguen las convenciones de C, así que tienes %d, %s, %f, %x, relleno con 0 o espacios, etc. Esto resulta mucho más limpio que armar cadenas a base de || y una pila de CAST.

LIKE vs GLOB en SQLite: coincidencia de patrones

Dos operadores, dos mundos distintos.

LIKE usa los comodines clásicos de SQL — % para cualquier secuencia de caracteres y _ para un único carácter — y no distingue entre mayúsculas y minúsculas en ASCII:

SELECT 'Alice'  LIKE 'a%'      AS alice_match;
SELECT 'README' LIKE '_____'   AS five_chars;
SELECT 'foo.txt' LIKE '%.txt'  AS is_text_file;

GLOB usa los comodines típicos de la shell de Unix: * para cualquier secuencia, ? para un solo carácter y [abc] para clases de caracteres. Eso sí, distingue entre mayúsculas y minúsculas:

SELECT 'Alice'  GLOB 'A*'         AS alice_match;
SELECT 'Alice'  GLOB 'a*'         AS lowercase_match;
SELECT 'log7'   GLOB 'log[0-9]'   AS digit_class;

La regla para elegir entre ambos: usa LIKE para coincidencias al estilo humano tipo "empieza con", "contiene" o "termina con". Usa GLOB cuando importe distinguir mayúsculas y minúsculas o cuando necesites clases de caracteres. Ambos pueden aprovechar índices, pero solo si el patrón está anclado al inicio ('foo%', no '%foo'); un comodín al principio obliga a escanear toda la tabla.

Dividir cadenas en SQLite: aquí no hay SPLIT

SQLite no trae una función SPLIT_STRING. Los dos apaños prácticos son:

-- Extraer una parte usando INSTR + SUBSTR
WITH addresses AS (
    SELECT 'rosa@example.com'   AS email UNION ALL
    SELECT 'boris@coddy.tech'   UNION ALL
    SELECT 'ada@lovelace.org'
)
SELECT
    SUBSTR(email, 1, INSTR(email, '@') - 1)  AS local_part,
    SUBSTR(email, INSTR(email, '@') + 1)     AS domain
FROM addresses;

Para dividir una cadena por un delimitador y obtener varias filas, lo más limpio es usar json_each sobre un array JSON, o bien una CTE recursiva. Veremos ambos enfoques en capítulos posteriores; por ahora basta con que sepas que "dame cada palabra" no se resuelve en una sola línea en SQLite.

Ejemplo práctico: limpiar nombres

Vamos a juntar todo lo visto. Imagina una tabla users con nombres para mostrar hechos un desastre: espacios de sobra, mayúsculas y minúsculas mezcladas y títulos opcionales tipo "Dr. " o "Mr. " que quieres quitar:

WITH users(id, display_name) AS (
    VALUES
      (1, '  Dr. Ada Lovelace  '),
      (2, 'mr. Boris Karloff'),
      (3, 'Rosa  Parks'),
      (4, '   alan TURING')
)
SELECT
    id,
    display_name AS original,
    TRIM(
      REPLACE(
        REPLACE(LOWER(TRIM(display_name)), 'dr. ', ''),
        'mr. ', ''
      )
    ) AS cleaned
FROM users;

La expresión se lee de adentro hacia afuera: primero quitamos los espacios externos, pasamos a minúsculas, eliminamos los títulos y volvemos a recortar por si al quitar el título quedó un espacio al inicio. Cada paso es una sola función — la complejidad aparece al apilarlas. Cuando la pila pasa de tres o cuatro niveles, es buena señal para usar una columna generada (Capítulo: Características avanzadas) o hacer la limpieza durante la importación de datos.

Lo que te llevas

• || para concatenar; NULL envenena el resultado, así que recurre a COALESCE.
• SUBSTR e INSTR juntos cubren la mayoría de los casos de "buscar y cortar".
• REPLACE sustituye todas las apariciones de una subcadena literal.
• TRIM y compañía aceptan un conjunto de caracteres personalizado, no solo espacios en blanco.
• printf es la herramienta adecuada para dar formato a la salida.
• LIKE para comodines SQL sin distinguir mayúsculas, GLOB para patrones tipo shell que sí distinguen mayúsculas.

Siguiente: funciones numéricas

Resueltas las cadenas, la parada obvia son los números: redondeos, valores absolutos, peculiaridades de la división y las funciones matemáticas que SQLite agregó en versiones recientes. Eso lo vemos en la próxima página.