mysql explode comma separated string
¿Puedes dividir/explotar un campo en una consulta MySQL? (16)
Acabo de tener un problema similar con un campo como el que resolví de otra manera. Mi caso de uso necesitaba tomar esos identificadores en una lista separada por comas para usar en una unión.
Pude resolverlo usando un Me gusta, pero fue más fácil porque además del delimitador de comas los ID también se citaban así:
keys "1","2","6","12"
Por eso, pude hacer un LIKE
SELECT twwf.id, jtwi.id joined_id FROM table_with_weird_field twwf INNER JOIN join_table_with_ids jtwi ON twwf.delimited_field LIKE CONCAT("%/"", jtwi.id, "/"%")
Básicamente, esto solo busca ver si la identificación de la mesa a la que intenta unirse aparece en el conjunto y en ese momento puede unirse fácilmente y devolver sus registros. También podría simplemente crear una vista a partir de algo como esto.
Funcionó bien para mi caso de uso, donde lidiaba con un plugin de Wordpress que administraba las relaciones de la manera descrita. Sin embargo, las comillas realmente ayudan porque de lo contrario corre el riesgo de coincidencias parciales (aka - id 1 dentro de 18, etc.).
Tengo que crear un informe sobre algunas terminaciones de estudiantes. Los estudiantes pertenecen a un cliente. Aquí están las tablas (simplificadas para esta pregunta).
CREATE TABLE `clients` (
`clientId` int(10) unsigned NOT NULL auto_increment,
`clientName` varchar(100) NOT NULL default '''',
`courseNames` varchar(255) NOT NULL default ''''
)
El campo courseNames contiene una cadena de nombres de curso delimitados por comas, por ejemplo, "AB01, AB02, AB03"
CREATE TABLE `clientenrols` (
`clientEnrolId` int(10) unsigned NOT NULL auto_increment,
`studentId` int(10) unsigned NOT NULL default ''0'',
`courseId` tinyint(3) unsigned NOT NULL default ''0''
)
El campo courseId aquí es el índice del nombre del curso en el campo clients.courseNames . Entonces, si los courseNames del cliente son "AB01, AB02, AB03", y el courseId del courseId de la inscripción es 2 , entonces el alumno está en AB03.
¿Hay alguna forma de que pueda hacer una única selección en estas tablas que incluya el nombre del curso? Tenga en cuenta que habrá estudiantes de diferentes clientes (y por lo tanto, tendrán diferentes nombres de cursos, no todos son secuenciales, por ejemplo: "NW01, NW03")
Básicamente, si pudiera dividir ese campo y devolver un solo elemento de la matriz resultante, eso sería lo que estoy buscando. Esto es lo que quiero decir en pseudocódigo mágico:
SELECT e.`studentId`, SPLIT(",", c.`courseNames`)[e.`courseId`]
FROM ...
Al ver que es una pregunta bastante popular, la respuesta es SÍ.
Para una columna de column en la tabla que contiene todos sus valores separados por coma:
CREATE TEMPORARY TABLE temp (val CHAR(255));
SET @S1 = CONCAT("INSERT INTO temp (val) VALUES (''",REPLACE((SELECT GROUP_CONCAT( DISTINCT `column`) AS data FROM `table`), ",", "''),(''"),"'');");
PREPARE stmt1 FROM @s1;
EXECUTE stmt1;
SELECT DISTINCT(val) FROM temp;
Recuerde, sin embargo, no almacenar CSV en su base de datos
Per @Mark Amery: como esto traduce los valores separados por coma en una INSERT , tenga cuidado al ejecutarlo en datos no analizados
Solo para reiterar, no almacene CSV en su base de datos; esta función está pensada para traducir CSV a una estructura de base de datos sensible y no para ser utilizada en ningún lugar de su código. Si tiene que usarlo en producción, vuelva a pensar su estructura de base de datos
Así es como lo hace para SQL Server. Alguien más puede traducirlo a MySQL. Análisis de valores de CSV en filas múltiples .
SELECT Author,
NullIf(SubString('','' + Phrase + '','' , ID , CharIndex('','' , '','' + Phrase + '','' , ID) - ID) , '''') AS Word
FROM Tally, Quotes
WHERE ID <= Len('','' + Phrase + '','') AND SubString('','' + Phrase + '','' , ID - 1, 1) = '',''
AND CharIndex('','' , '','' + Phrase + '','' , ID) - ID > 0
La idea es unir cruz a una tabla predefinida Tally que contenga el número entero 1 a 8000 (o el número lo suficientemente grande) y ejecutar SubString para encontrar la posición correcta, palabra SubString
Basado en la respuesta de Alex anterior ( https://.com/a/11022431/1466341 ), se me ocurrió una solución aún mejor. Solución que no contiene una identificación de registro exacta.
Suponiendo que la lista separada por comas está en la tabla data.list , y contiene una lista de códigos de otra tabla classification.code , puede hacer algo como:
SELECT
d.id, d.list, c.code
FROM
classification c
JOIN data d
ON d.list REGEXP CONCAT(''[[:<:]]'', c.code, ''[[:>:]]'');
Entonces, si tiene tablas y datos como este:
CLASSIFICATION (code varchar(4) unique): (''A''), (''B''), (''C''), (''D'')
MY_DATA (id int, list varchar(255)): (100, ''C,A,B''), (150, ''B,A,D''), (200,''B'')
encima de SELECT volverá
(100, ''C,A,B'', ''A''),
(100, ''C,A,B'', ''B''),
(100, ''C,A,B'', ''C''),
(150, ''B,A,D'', ''A''),
(150, ''B,A,D'', ''B''),
(150, ''B,A,D'', ''D''),
(200, ''B'', ''B''),
Bueno, nada de lo que utilicé funcionó, así que decidí crear una función dividida muy simple, espero que ayude:
DECLARE inipos INTEGER;
DECLARE endpos INTEGER;
DECLARE maxlen INTEGER;
DECLARE item VARCHAR(100);
DECLARE delim VARCHAR(1);
SET delim = ''|'';
SET inipos = 1;
SET fullstr = CONCAT(fullstr, delim);
SET maxlen = LENGTH(fullstr);
REPEAT
SET endpos = LOCATE(delim, fullstr, inipos);
SET item = SUBSTR(fullstr, inipos, endpos - inipos);
IF item <> '''' AND item IS NOT NULL THEN
USE_THE_ITEM_STRING;
END IF;
SET inipos = endpos + 1;
UNTIL inipos >= maxlen END REPEAT;
Es posible explotar una cadena en una instrucción MySQL SELECT.
Primero genere una serie de números hasta la mayor cantidad de valores delimitados que desea explotar. Ya sea desde una tabla de enteros, o uniendo números. A continuación, se generan 100 filas con los valores de 1 a 100. Se puede ampliar fácilmente para obtener intervalos más amplios (agregue otra consulta secundaria que proporcione los valores de 0 a 9 para cientos, lo que da de 0 a 999, etc.).
SELECT 1 + units.i + tens.i * 10 AS aNum
FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
Esto se puede combinar cruzado contra su mesa para darle los valores. Tenga en cuenta que utiliza SUBSTRING_INDEX para obtener el valor delimitado hasta cierto valor y luego usa SUBSTRING_INDEX para obtener ese valor, excluyendo los anteriores.
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(clients.courseNames, '','', sub0.aNum), '','', -1) AS a_course_name
FROM clients
CROSS JOIN
(
SELECT 1 + units.i + tens.i * 10 AS aNum, units.i + tens.i * 10 AS aSubscript
FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
Como puede ver, hay un pequeño problema aquí que el último valor delimitado se repite muchas veces. Para deshacerse de esto, debe limitar el rango de números según la cantidad de delimitadores que haya. Esto se puede hacer tomando la longitud del campo delimitado y comparándolo con la longitud del campo delimitado con los delimitadores cambiados a '''' (para eliminarlos). De esto puedes obtener la cantidad de delimitadores:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(clients.courseNames, '','', sub0.aNum), '','', -1) AS a_course_name
FROM clients
INNER JOIN
(
SELECT 1 + units.i + tens.i * 10 AS aNum
FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
ON (1 + LENGTH(clients.courseNames) - LENGTH(REPLACE(clients.courseNames, '','', ''''))) >= sub0.aNum
En el campo de ejemplo original, podría (por ejemplo) contar el número de estudiantes en cada curso basado en esto. Tenga en cuenta que he cambiado la sub consulta que obtiene el rango de números para traer de vuelta 2 números, 1 se usa para determinar el nombre del curso (ya que estos se basan en comenzar en 1) y el otro obtiene el subíndice (ya que se basan en a las 0).
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(clients.courseNames, '','', sub0.aNum), '','', -1) AS a_course_name, COUNT(clientenrols.studentId)
FROM clients
INNER JOIN
(
SELECT 1 + units.i + tens.i * 10 AS aNum, units.i + tens.i * 10 AS aSubscript
FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
) sub0
ON (1 + LENGTH(clients.courseNames) - LENGTH(REPLACE(clients.courseNames, '','', ''''))) >= sub0.aNum
LEFT OUTER JOIN clientenrols
ON clientenrols.courseId = sub0.aSubscript
GROUP BY a_course_name
Como puede ver, es posible, pero bastante desordenado. Y con poca oportunidad de usar índices, no va a ser eficiente. Además, el rango debe hacer frente al mayor número de valores delimitados, y funciona al excluir muchos duplicados; si el número máximo de valores delimitados es muy grande, esto disminuirá drásticamente las cosas. En general, es mucho mejor simplemente normalizar correctamente la base de datos.
Esto es lo que tengo hasta ahora (lo encontré en la página mencionada por Ben Alpert ):
SELECT REPLACE(
SUBSTRING(
SUBSTRING_INDEX(c.`courseNames`, '','', e.`courseId` + 1)
, LENGTH(SUBSTRING_INDEX(c.`courseNames`, '','', e.`courseId`)
) + 1)
, '',''
, ''''
)
FROM `clients` c INNER JOIN `clientenrols` e USING (`clientId`)
Hasta ahora, quería mantener esas listas separadas por comas en mi SQL db, ¡consciente de todas las advertencias!
Seguí pensando que tienen beneficios sobre las tablas de búsqueda (que proporcionan un camino hacia una base de datos normalizada). Después de algunos días de rechazarlo, he visto la luz :
- El uso de tablas de búsqueda NO está causando más código que esas feas operaciones de cadena cuando se usan valores separados por comas en un campo.
- La tabla de búsqueda permite formatos de números nativos y, por lo tanto, NO es más grande que esos campos de csv. Aunque es MÁS PEQUEÑO.
- Las operaciones de cadena implicadas son escasas en el código de lenguaje de alto nivel (SQL y PHP), pero caras en comparación con el uso de matrices de enteros.
- Las bases de datos no están pensadas para ser legibles por humanos, y es más estúpido tratar de apegarse a las estructuras debido a su legibilidad / editabilidad directa, como yo lo hice.
En resumen, hay una razón por la cual no existe una función SPLIT () nativa en MySQL.
Hay una manera más fácil, tener una tabla de enlaces, es decir:
Tabla 1: clientes, información del cliente, bla, bla, bla
Tabla 2: cursos, información del curso, bla, bla
Tabla 3: clientid, courseid
Luego haz un JOIN y estarás fuera de las carreras.
He resuelto este tipo de problema con un patrón de expresión regular. Tienden a ser más lentos que las consultas regulares, pero es una manera fácil de recuperar datos en una columna de consulta delimitada por comas
SELECT *
FROM `TABLE`
WHERE `field` REGEXP '',?[SEARCHED-VALUE],?'';
el signo de interrogación codicioso ayuda a buscar al principio o al final de la cadena.
Espero que ayude a cualquiera en el futuro
La única función de división de cadenas de MySQL es SUBSTRING_INDEX(str, delim, count) . Puede usar esto para, por ejemplo:
Devuelve el artículo antes del primer separador en una cadena:
mysql> SELECT SUBSTRING_INDEX(''foo#bar#baz#qux'', ''#'', 1); +--------------------------------------------+ | SUBSTRING_INDEX(''foo#bar#baz#qux'', ''#'', 1) | +--------------------------------------------+ | foo | +--------------------------------------------+ 1 row in set (0.00 sec)Devuelve el artículo después del último separador en una cadena:
mysql> SELECT SUBSTRING_INDEX(''foo#bar#baz#qux'', ''#'', -1); +---------------------------------------------+ | SUBSTRING_INDEX(''foo#bar#baz#qux'', ''#'', -1) | +---------------------------------------------+ | qux | +---------------------------------------------+ 1 row in set (0.00 sec)Devuelve todo antes del tercer separador en una cadena:
mysql> SELECT SUBSTRING_INDEX(''foo#bar#baz#qux'', ''#'', 3); +--------------------------------------------+ | SUBSTRING_INDEX(''foo#bar#baz#qux'', ''#'', 3) | +--------------------------------------------+ | foo#bar#baz | +--------------------------------------------+ 1 row in set (0.00 sec)Devuelve el segundo elemento en una cadena, encadenando dos llamadas:
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(''foo#bar#baz#qux'', ''#'', 2), ''#'', -1); +----------------------------------------------------------------------+ | SUBSTRING_INDEX(SUBSTRING_INDEX(''foo#bar#baz#qux'', ''#'', 2), ''#'', -1) | +----------------------------------------------------------------------+ | bar | +----------------------------------------------------------------------+ 1 row in set (0.00 sec)
En general, una forma simple de obtener el enésimo elemento de una cadena separada por # (suponiendo que usted sabe que definitivamente tiene al menos n elementos) es hacer:
SUBSTRING_INDEX(SUBSTRING_INDEX(your_string, ''#'', n), ''#'', -1);
La llamada SUBSTRING_INDEX interna descarta el n-ésimo separador y todo lo que sigue, y luego la llamada externa SUBSTRING_INDEX descarta todo excepto el elemento final que queda.
Si desea una solución más sólida que devuelva NULL si solicita un elemento que no existe (por ejemplo, solicitando el 5 ° elemento de ''a#b#c#d'' ), puede contar los delimitadores utilizando REPLACE y luego devuelve condicionalmente NULL usando IF() :
IF(
LENGTH(your_string) - LENGTH(REPLACE(your_string, ''#'', '''')) / LENGTH(''#'') < n - 1,
NULL,
SUBSTRING_INDEX(SUBSTRING_INDEX(your_string, ''#'', n), ''#'', -1)
)
Por supuesto, esto es bastante feo y difícil de entender! Por lo que es posible que desee envolverlo en una función:
CREATE FUNCTION split(string TEXT, delimiter TEXT, n INT)
RETURNS TEXT DETERMINISTIC
RETURN IF(
(LENGTH(string) - LENGTH(REPLACE(string, delimiter, ''''))) / LENGTH(delimiter) < n - 1,
NULL,
SUBSTRING_INDEX(SUBSTRING_INDEX(string, delimiter, n), delimiter, -1)
);
A continuación, puede utilizar la función de esta manera:
mysql> SELECT SPLIT(''foo,bar,baz,qux'', '','', 3);
+----------------------------------+
| SPLIT(''foo,bar,baz,qux'', '','', 3) |
+----------------------------------+
| baz |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SPLIT(''foo,bar,baz,qux'', '','', 5);
+----------------------------------+
| SPLIT(''foo,bar,baz,qux'', '','', 5) |
+----------------------------------+
| NULL |
+----------------------------------+
1 row in set (0.00 sec)
mysql> SELECT SPLIT(''foo###bar###baz###qux'', ''###'', 2);
+------------------------------------------+
| SPLIT(''foo###bar###baz###qux'', ''###'', 2) |
+------------------------------------------+
| bar |
+------------------------------------------+
1 row in set (0.00 sec)
Puedes crear una función para esto:
/**
* Split a string by string (Similar to the php function explode())
*
* @param VARCHAR(12) delim The boundary string (delimiter).
* @param VARCHAR(255) str The input string.
* @param INT pos The index of the string to return
* @return VARCHAR(255) The (pos)th substring
* @return VARCHAR(255) Returns the [pos]th string created by splitting the str parameter on boundaries formed by the delimiter.
* @{@example
* SELECT SPLIT_STRING(''|'', ''one|two|three|four'', 1);
* This query
* }
*/
DROP FUNCTION IF EXISTS SPLIT_STRING;
CREATE FUNCTION SPLIT_STRING(delim VARCHAR(12), str VARCHAR(255), pos INT)
RETURNS VARCHAR(255) DETERMINISTIC
RETURN
REPLACE(
SUBSTRING(
SUBSTRING_INDEX(str, delim, pos),
LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1
),
delim, ''''
);
Convirtiendo el pseudocódigo mágico para usar esto, tendrías:
SELECT e.`studentId`, SPLIT_STRING('','', c.`courseNames`, e.`courseId`)
FROM...
Si necesita obtener una tabla de cadena con delimitadores:
SET @str = ''function1;function2;function3;function4;aaa;bbbb;nnnnn'';
SET @delimeter = '';'';
SET @sql_statement = CONCAT(''SELECT ''''''
,REPLACE(@str, @delimeter, '''''' UNION ALL SELECT '''''')
,'''''''');
SELECT @sql_statement;
SELECT ''function1'' UNION ALL SELECT ''function2'' UNION ALL SELECT ''function3'' UNION ALL SELECT ''function4'' UNION ALL SELECT ''aaa'' UNION ALL SELECT ''bbbb'' UNION ALL SELECT ''nnnnn''
Sobre la base de la solución de Alwin Kesler, aquí hay un ejemplo más práctico del mundo real.
Suponiendo que la lista separada por comas está en my_table.list, y es una lista de ID para my_other_table.id, puede hacer algo como:
SELECT
*
FROM
my_other_table
WHERE
(SELECT list FROM my_table WHERE id = ''1234'') REGEXP CONCAT('',?'', my_other_table.id, '',?'');
Usé la lógica anterior pero la modifiqué ligeramente. Mi entrada es de formato: "apple: 100 | pinapple: 200 | orange: 300" almacenado en una variable @updtAdvanceKeyVal
Aquí está el bloque de funciones:
set @res = "";
set @i = 1;
set @updtAdvanceKeyVal = updtAdvanceKeyVal;
REPEAT
-- set r = replace(SUBSTRING(SUBSTRING_INDEX(@updtAdvanceKeyVal, "|", @i),
-- LENGTH(SUBSTRING_INDEX(@updtAdvanceKeyVal, "|", @i -1)) + 1),"|","");
-- wrapping the function in "replace" function as above causes to cut off a character from
-- the 2nd splitted value if the value is more than 3 characters. Writing it in 2 lines causes no such problem and the output is as expected
-- sample output by executing the above function :
-- orange:100
-- pi apple:200 !!!!!!!!strange output!!!!!!!!
-- tomato:500
set @r = SUBSTRING(SUBSTRING_INDEX(@updtAdvanceKeyVal, "|", @i),
LENGTH(SUBSTRING_INDEX(@updtAdvanceKeyVal, "|", @i -1)) + 1);
set @r = replace(@r,"|","");
if @r <> "" then
set @key = SUBSTRING_INDEX(@r, ":",1);
set @val = SUBSTRING_INDEX(@r, ":",-1);
select @key, @val;
end if;
set @i = @i + 1;
until @r = ""
END REPEAT;
SELECT
tab1.std_name, tab1.stdCode, tab1.payment,
SUBSTRING_INDEX(tab1.payment, ''|'', 1) as rupees,
SUBSTRING(tab1.payment, LENGTH(SUBSTRING_INDEX(tab1.payment, ''|'', 1)) + 2,LENGTH(SUBSTRING_INDEX(tab1.payment, ''|'', 2))) as date
FROM (
SELECT DISTINCT
si.std_name, hfc.stdCode,
if(isnull(hfc.payDate), concat(hfc.coutionMoneyIn,''|'', year(hfc.startDtae), ''-'', monthname(hfc.startDtae)), concat(hfc.payMoney, ''|'', monthname(hfc.payDate), ''-'', year(hfc.payDate))) AS payment
FROM hostelfeescollection hfc
INNER JOIN hostelfeecollectmode hfm ON hfc.tranId = hfm.tranId
INNER JOIN student_info_1 si ON si.std_code = hfc.stdCode
WHERE hfc.tranId = ''TRAN-AZZZY69454''
) AS tab1