Guía completa para trabajar con subcadenas en SQL

En este artículo, discutiremos cómo trabajar con subcadenas en SQL. Esto implica aprender varios enfoques, por lo que te mostraremos sus pormenores.

Para trabajar con subcadenas en SQL, primero necesitamos saber qué es una cadena. En programación, una cadena es cualquier tipo de dato textual (un título, una descripción, un nombre). Una cadena consta de cualquier número y tipo de caracteres. En una base de datos SQL, las cadenas se almacenan normalmente como tipos de datos CHAR o VARCHAR.

Una subcadena es parte de una cadena. En otras palabras, una subcadena es una parte más pequeña de la secuencia de caracteres. Si tuviéramos la cadena 'Curso SQL', 'SQL' sería una subcadena de esa cadena. Lo hemos ilustrado en la siguiente imagen:

Observe que la imagen (y una cadena) no ignora los espacios en blanco; los espacios en blanco también son caracteres en una cadena.

Prepárese para trabajar con subcadenas SQL

Conocer la familia de funciones de texto SQL es obligatorio cuando se trabaja con cadenas. Aunque mantendremos los ejemplos de subcadenas lo más sencillos posible, necesitaremos funciones de texto. El uso de estas funciones a menudo requiere conocer otros conceptos de SQL, como JOINs, agrupación de datos, filtrado de datos y uso de subconsultas. Puedes practicar todos estos conceptos en nuestro Ejercicio de SQL curso. Hay ocho cursos de práctica con un total de más de 950 desafíos de codificación, por lo que te dolerán los dedos al final de este curso :) Pero esa es la cuestión, ya que la práctica es realmente la única manera de adquirir fluidez en SQL.

Utilizaremos el mismo enfoque aquí y te mostraremos tantos ejemplos como nos permita este artículo. A la hora de extraer una subcadena de una cadena en SQL, la función de texto SUBSTRING() parece una opción obvia. Sin embargo, no es la única. También existen otras, como el operador LIKE y las funciones de texto LEFT() y RIGHT().

Aunque esto pueda parecer fácil, trabajar con subcadenas SQL no siempre es sencillo. Tienes que conocer los matices de cada función que quieras aplicar, cómo funciona la indexación en SQL y cómo maneja SQL los datos textuales. Muchos profesionales de los datos confirmarán que trabajar con subcadenas en SQL puede convertirse en una experiencia dolorosa y frustrante.

Bueno, nos aseguraremos de que no sea dolorosa para ti. Empecemos.

Ejemplo de conjunto de datos

Trabajaremos con la tabla clients. Como su nombre indica, es una lista de los clientes de la empresa.

Puede crear la tabla utilizando esta consulta. Tenga en cuenta que la fecha de nacimiento se almacena en un formato de datos de texto.

Empecemos por extraer las subcadenas.

Comprobar si una cadena contiene una subcadena en SQL

Una forma común de comprobar si un texto contiene una determinada subcadena en SQL es utilizando el operador LIKE o ILIKE. Se utilizan con la cláusula WHERE para filtrar datos. Ambos operadores encontrarán cadenas con una subcadena como la definida en su patrón. La única diferencia es que LIKE distingue entre mayúsculas y minúsculas, mientras que ILIKE no.

Utilice LIKE e ILIKE para comprobar si el texto contiene una subcadena

Su colega le dice que un cliente llamó con un mensaje para usted. Como el compañero tenía prisa, se olvidó de anotar el nombre del cliente. El cliente se llamaba Isaac, Dave o algo así.

Puedes intentar localizar al cliente llamado Isaac o Dave escribiendo esta consulta:

SELECT *
FROM clients
WHERE first_name LIKE 'Dave' OR first_name LIKE 'Isaac';

En esta consulta, seleccionas todas las columnas de la tabla. Luego utilizas la cláusula WHERE y el operador LIKE. Pones los nombres 'Dave' e 'Isaac' entre comillas simples en el operador LIKE. La condición en la cláusula WHERE se puede leer como: "Devolver todos los clientes con Dave o Isaac como nombre".

Ejecutemos el código y veremos... ¡que no hay absolutamente nada! El código no devuelve ninguna fila. ¡Código estúpido! Si miramos la tabla, vemos que hay clientes llamados Isaac Guardiola y Dave Trotter:

Entonces, ¿por qué no funciona? La consulta anterior asumirá que la subcadena empieza por 'Dave' o 'Isaac'. Si observamos la tabla, veremos que la cadena empieza por el saludo.

Para que la consulta funcione, debemos utilizar el comodín '%' con LIKE. Este comodín sirve para buscar una subcadena al principio, al final y en cualquier punto intermedio. Todo depende de dónde coloques el comodín. A continuación se explica brevemente cómo utilizarlo.

Reescribamos la consulta:

SELECT *
FROM clients
WHERE first_name LIKE '%Dave%' OR first_name LIKE '%Isaac%';

Hemos aprendido que los comodines antes y después de la subcadena que queremos encontrar significan que la consulta buscará esta subcadena en cualquier lugar de la cadena. Esto nos permite omitir el saludo delante de cada nombre.

Este es el resultado:

Para darle otro ejemplo del uso de '%' con LIKE, también puede buscar todos los misters en nuestra lista de clientes.

Para ello, coloque el comodín después de la subcadena que está buscando, ya que la subcadena empieza por 'Sr.':

SELECT *
FROM clients
WHERE first_name LIKE 'Mr.%';

Este es el resultado.

Probablemente te hayas dado cuenta de que escribimos las subcadenas en LIKE exactamente igual que en la tabla en lo que respecta a las mayúsculas y minúsculas. Esto se debe a que el operador LIKE distingue entre mayúsculas y minúsculas.

Si quieres evitarlo o no estás seguro de cómo se formatean los datos, puedes utilizar el operador ILIKE. Como no distingue entre mayúsculas y minúsculas, puedes escribir la subcadena como quieras:

SELECT *
FROM clients
WHERE first_name ILIKE '%dave%' 
OR first_name ILIKE '%iSAaC%';

La salida es la misma que con LIKE:

Puedes aprender más sobre LIKE (y NOT LIKE) en este artículo.

La función SUBSTRING()

SUBSTRING() es una función SQL utilizada para extraer la subcadena de una cadena. Permite especificar la cadena de la que se desea extraer la subcadena. La subcadena se define especificando su posición inicial dentro de la cadena y su longitud.

Esta es la sintaxis de la función:

SUBSTRING(string, start_position, length)

Como cualquier función SQL, SUBSTRING() toma argumentos entre sus paréntesis. Dos argumentos son obligatorios:

• string - La cadena que desea buscar; puede ser una constante o una columna/expresión.
• start_position - Un valor entero que define la posición (el número ordinal del carácter) en la que comienza la subcadena, es decir, la posición del primer carácter de la subcadena.

El tercer argumento es length, que es opcional. Define la longitud de una subcadena como el número de caracteres que contendrá. Si se omite este argumento, la subcadena será el resto de la cadena, contando desde la posición inicial.

Veamos algunos ejemplos para aclarar todo esto.

Utilice SUBSTRING() para extraer una subcadena de una cadena constante

Una cadena constante es cualquier cadena que escriba entre comillas simples como primer argumento en SUBSTRING().

Por ejemplo, la cadena constante en el siguiente código es 'Ejercicio de SQL Curso', y queremos extraer 'Curso Práctico'.

SELECT SUBSTRING ('Ejercicio de SQL Course', 5) 
AS substring_extract

El número entero 5 es el argumento start_position. Significa que la subcadena comienza a partir del quinto carácter de la cadena.

¿Por qué elegir 5 si sólo hay tres letras ('SQL') delante de nuestra subcadena? ¿No debería empezar la subcadena en la cuarta posición? Tenga cuidado: ¡El espacio en blanco también cuenta como un carácter en una cadena!

Como hemos omitido el argumento length, el código devolverá el resto de la cadena como una subcadena. En otras palabras, el código devuelve este resultado:

Utilice SUBSTRING() para extraer una subcadena de una columna

En lugar de un valor constante, también puede especificar una columna de la que desee extraer una subcadena.

Vamos a mostrar el nombre y apellidos del cliente pero sin el saludo (Sr./Sra./Mx.):

SELECT SUBSTRING (first_name, 5) AS first_name,
	 last_name
FROM clients;

Volvemos a utilizar la función SUBSTRING(). El primer argumento es el nombre de la columna, que es first_name. El segundo argumento es la posición inicial de la subcadena, que es cinco. Recuerda contar los espacios en blanco. Hay uno después del saludo, por lo que 'Sr. ' consta de cuatro caracteres. El quinto carácter es la primera letra del nombre del cliente.

Hemos omitido la longitud de la subcadena, y la segunda columna seleccionada es el apellido del cliente, por lo que el código devuelve esto:

Utilizar SUBSTRING() para extraer una subcadena de longitud definida

Ahora dejaremos de evitar el tercer argumento SUBSTRING(). En este ejemplo, mostraremos cómo extraer el día y el mes de nacimiento de los clientes:

SELECT first_name,
	 last_name,
	 SUBSTRING (date_of_birth, 1, 6) AS birthday
FROM clients;

Después de seleccionar los nombres y apellidos de los clientes, utilizamos la función SUBSTRING(). Utilizaremos la columna date_of_birth para extraer la fecha de nacimiento del cliente (mes y día).

Después de pasar la columna como argumento, especificamos la posición inicial. El número 1 significa que la subcadena se extraerá a partir del primer carácter de la cadena. El número 6 significa que la subcadena constará de seis caracteres: dos caracteres para el día, un punto, dos caracteres para el mes y otro punto.

Eche un vistazo al resultado:

Todos los clientes están ahí, junto con sus cumpleaños extraídos de la forma que queríamos. Esta vez no nos importa mostrar el saludo.

Uso de SUBSTRING() con otras funciones para encontrar un índice de subcadena

Existen funciones que se pueden utilizar para encontrar un índice dentro de la cadena. En MySQL y PostgreSQL, esta función se llama POSITION(); en SQL Server se llama CHARINDEX(). Puedes especificar la subcadena que estás buscando y estas funciones te devolverán su posición dentro de la cadena.

Estas funciones son extremadamente útiles en combinación con SUBSTRING(). Hablemos primero de POSITION() y luego veremos un ejemplo con CHARINDEX().

La función POSITION() le permite especificar la subcadena y encontrará su posición inicial. Utilicémosla para extraer el nombre de usuario de cada cliente a partir de su dirección de correo electrónico:

SELECT first_name,
	 last_name,
	 email,
	 POSITION('@' IN email) AS at_position,
	 SUBSTRING(email, 1, POSITION('@' IN email)-1) AS username
FROM clients;

Arriba, usamos POSITION() para encontrar la '@' en la dirección de correo electrónico de cada usuario. Lo necesitas porque todo lo que viene antes de ese carácter es el nombre del usuario. La sintaxis de POSITION() es sencilla: basta con especificar el carácter de búsqueda entre comillas simples, seguido de la palabra clave IN y el nombre de las columnas en las que desea buscar.

Esta columna sólo devuelve la posición del carácter. Tenemos que incluirla en SUBSTRING() para obtener el nombre de usuario. Los dos primeros argumentos en SUBSTRING() son familiares: estamos buscando en la columna email, y queremos que nuestra subcadena (un nombre de usuario) empiece por el primer carácter de la cadena.

El tercer argumento de SUBSTRING() es la longitud de la subcadena. La longitud del nombre de usuario es el número de caracteres antes y después de '@'. En otras palabras, la longitud del nombre de usuario es la posición de '@' menos uno. ¿Por qué menos? Porque si no, '@' formaría parte del nombre de usuario.

Podemos comprobar el resultado. En el 'iguardiola@gmail.com', el símbolo '@' está en la posición 11, como se muestra en la columna at_position. La columna nombre de usuario muestra la subcadena anterior a '@', que es efectivamente 'iguardiola' para este cliente. Puede comprobar el resto de los resultados del mismo modo.

El mismo código en SQL Server debería utilizar la función CHARINDEX():

SELECT first_name,
	 last_name,
	 email,
	 CHARINDEX('@', email) AS at_position,
	 SUBSTRING(email, 1, CHARINDEX('@', email)-1) AS username
FROM clients;

La lógica es similar a la función POSITION(): se especifica la subcadena que se busca y luego la columna, con los argumentos separados por una coma.

El código de salida es el mismo.

Ya hemos terminado con SUBSTRING(). Pero si necesitas más práctica, aquí tienes otros cinco ejemplos de SUBSTRING().

LEFT() y RIGHT() en SQL

Otra forma de extraer una subcadena es utilizar las funciones LEFT() o RIGHT().

LEFT() extrae la subcadena por la izquierda, permitiéndonos definir la longitud de la subcadena. La función RIGHT() hace lo mismo pero por la derecha.

Utilizar LEFT() y RIGHT() para extraer una subcadena

Utilicemos estas dos funciones para extraer algunas subcadenas: las tres primeras letras de un apellido y el año de nacimiento.

El código es el siguiente.

SELECT first_name,
	 last_name,
	 LEFT(last_name, 3) AS last_name_substring,
	 RIGHT(date_of_birth, 4) AS year_of_birth
FROM clients;

Utilizamos la función LEFT() para obtener las tres primeras letras del apellido. Empieza por la izquierda y toma el número de caracteres especificado para crear una subcadena. La sintaxis es sencilla: especificamos el nombre de la columna y, a continuación, la longitud de la subcadena.

RIGHT() se utiliza para buscar el año de nacimiento. Empieza por la derecha y toma el número de caracteres especificado para crear una subcadena. Aquí, el primer argumento es la columna date_of_birth. Entonces contamos el número de caracteres que queremos desde la derecha, que es cuatro - esto devolverá el año de nacimiento.

Como LEFT() y RIGHT() son básicamente imágenes especulares la una de la otra, podríamos haber utilizado fácilmente cualquiera de estas funciones para encontrar las dos subcadenas necesarias.

Este es el resultado de la consulta anterior:

Muestra las tres primeras letras del apellido y el año de nacimiento.

Conviértase en el que saca todas las (sub)cadenas en SQL

Como analista de datos, se espera que seas tú quien tire de todas las (sub)cadenas. Uno de los trucos para lograrlo es conocer varias formas de trabajar con subcadenas, como las que te mostramos aquí.

Conocer distintas funciones para manejar subcadenas te permitirá elegir la que mejor se adapte a tu problema. No sólo obtendrás el resultado que deseas, sino que también acabarás con un código más eficiente y con menos líneas.

Para llegar a ese nivel, debes trabajar mucho con cadenas y utilizar a diario todas las funciones mencionadas. En otras palabras, necesitas escribir código. Si necesitas un entorno seguro para hacerlo, nuestro Ejercicio de SQL ofrece exactamente eso.

¡Buena suerte para convertirte en el maestro de las subcadenas SQL!