FUNCION MYSQL QUE SEPARA ITEMS DE UNA LISTA DE TEXTO

A veces, la información que se almacena en la base de datos son campos que contienen una lista de valores separados por comas.

PROVINCIAS
---------------------------------
Madrid, Cadiz, Sevilla, Cáceres

La función SPLIT_STR de MySQL permite separar valores de una cadena de texto acumulando los valores anteriores a la posición dada. Su sintaxis es:

SUBSTRING_INDEX(<nombre_campo>,",",index)

Para aplicar la instrucción SUBSTRING_INDEX a un campo de la base de datos, debemos indicar el índice y el carácter delimitador, que en nuestro caso es la coma. Veamos como se hace:

SUBSTRING_INDEX(PROVINCIAS,",",1)

La instrucción anterior devuelve el primer elemento de la lista, tal y como se ha indicado en el índice. Para recuperar el segundo elemento escribiriamos la siguiente función:

SUBSTRING(SUBSTRING_INDEX(PROVINCIAS,",",2),LENGTH(SUBSTRING_INDEX(PROVINCIAS,",",1))+1)

La función SUBSTRING_INDEX no es suficiente para extraer elementos de una lista, ya que no devuelve el elemento en cuestión, sino la suma de todos los elementos previos al indice. Por tanto, para recuperar cualquier elemento de una cadena de texto, hay que combinar la función SUBSTRING_INDEX con SUBSTRING. Por ejemplo, para recuperar el tercer elemento de la lista:

SUBSTRING(SUBSTRING_INDEX(PROVINCIAS,",",3),LENGTH(SUBSTRING_INDEX(PROVINCIAS,",",2))+1)

La instrucción anterior extrae los tres primeros valores de la lista y se usa la longitud del primer valor, para extraer la porción de texto que necesitamos. En nuestro ejemplo, la instrucción anterior devuelve:

, Sevilla

Si ahora deseamos extraer el cuarto valor, deberíamos ejecutar la siguiente instrucción:

SUBSTRING(SUBSTRING_INDEX(PROVINCIAS,",",4),LENGTH(SUBSTRING_INDEX(PROVINCIAS,",",3))+1)

, Cáceres

Para eliminar la coma que nos aparece en el resultado, debemos utilizar la instrucción REPLACE de la siguiente manera:

REPLACE(SUBSTRING(SUBSTRING_INDEX(PROVINCIAS,",",3),LENGTH(SUBSTRING_INDEX(PROVINCIAS,",",2))+1),",",'')

Con la función REPLACE indicamos que sustituya el carácter coma «,» por un espacio vacío. Ahora solo queda recopilar todo lo anterior dentro de una función MySQL.

DELIMITER $$
CREATE FUNCTION SPLIT_STR(x VARCHAR(255), delim VARCHAR(12), pos INT) RETURNS VARCHAR(255)
BEGIN
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '')

END$$
DELIMITER ;

CONCLUSIÓN

La función SPLIT_STR permite separar items de una cadena de texto sin acumular los anteriores. La función SUBSTRING_INDEX, es insuficiente para extraer valores de una cadena de texto y es necesario combinarla con otras funciones MySQL: SUBSTRING, LENGTH Y REPLACE, para extraer un dato de una cadena de texto.

REFERENCIAS

Para más información sobre las funciones empleadas:

• SUBSTRING_INDEX: w3school
• REPLACE: w3resource
• LENGTH: w3school

Por último, me gustaría ofrecer mi agradecimiento a Federico Cargnelutti autor de la función función SPLIT_STR y de donde tomé mi fuente de inspiración para realizar este artículo.