none
Extraer texto separado por comas en una sola tabla desde varios registros. RRS feed

  • Pregunta

  • Saludos. Explico mi problema a ver si me pueden echar una mano.

    Tengo una tabla con cientos de registros, en ella un campo de texto que tiene palabras separadas por "," ejemplo: "Pepe, Luis, Javier, Andrés"

    Necesito una consulta que muestre esos valores como tabla con un solo campo:

    Resultado de la consulta:

    Pepe
    Luis
    Javier
    Andrés

    Hasta aquí no es un problema porque he encontrado cientos de procedimientos que hacen esto con una cadena de texto pero el problema es que tengo que recorrer todos los registros de la tabla y hacer esto por cada uno de ellos, siendo el resultado final una tabla con los valores extraídos de todos los registros.
    Cualquier procedimiento de los que he encontrado por la web solo extrae la información de un registro, o una sola cadena de caracteres. No soy capaz de hacer que recorra todos los registros de la tabla.

    El segundo problema relacionado es que al final necesito que se agrupen los valores resultantes porque pueden existir valores duplicados entre registro y registro, ejemplo:

    Id/ Campo Palabras

    1 / "Pepe, Luis, Javier, Andrés"

    2 / "Sofía, Carlos, Pepe, Lorenzo"

    3 / "Juan, María, Martín, Pepe, Ginés"
    Por lo que necesito que la tabla resultante no me repita los valores:

    Pepe
    Luis
    Javier
    Andrés
    Sofía
    Carlos
    Lorenzo
    Juan
    María
    Martín

    ¿Hay alguna forma de montar esto?

    Gracias.

    jueves, 28 de julio de 2016 15:14

Respuestas

  • Efectivamente, esto es muy ineficiente. ¿Por qué? Pues porque si la tabla tiene 30000 registros hay que llamar 30000 veces a la función, que construye 30000 tablas temporales que tienen que unirse con un JOIN a la tabla principal. A diferencia de las funciones inline, que se optimizan conjuntamente con la sentencia que las llama, las funciones multilínea de tipo tabla se ejecutan por separado cada vez que hay que evaluarlas, por lo que no resultan eficientes si hay muchos registros.

    Para que realmente corra, hay que hacerlo todo en una sola pasada, lo cual implica que el código que ahora tienes por separado dentro de la función hay que combinarlo con la sentencia que recorre la tabla completa, para que se optimice conjuntamente. Sería algo parecido a lo que te pongo a continuación, pero tendrás que pulirlo porque lo he escrito de memoria y casi seguro tendrá algún error:

    Select distinct item
    from (select r.value('.','varchar(max)') as item
     from (cast N'<root><r>' + replace(Nombres,',','</r><r>') + '</r></root>' as XML).nodes('//root/r') as records(r))
     from Pruebas

    Si no cuela todo seguido con una subconsulta tal como te lo he escrito, puedes probar a refactorizarlo usando una Common Table Expression con un WITH.

    domingo, 31 de julio de 2016 10:52

Todas las respuestas

  • De forma esquemática hay que hacer más o menos esto:

    1) Escribe la función que dices que ya sabes hacer, que toma un texto con comas y te devuelve una tabla con el texto dividido en varias filas.

    2) Escribe una sentencia del tipo "Select laColumna from laTabla CROSS APPLY laFunción(columna) ON ...". El cross apply hace que se llame a la función por cada registro de la tabla y los resultados se combinan como si fuera un JOIN.

    3) Al resultado hazle un DISTINCT o un GROUP BY para eliminar los duplicados.

    Si necesitas un ejemplo detallado, ponnos aquí las sentencias necesarias para crear la tabla y rellenarla con datos de prueba, así como la función que ya tienes para separar el texto por las comas, y con eso te podemos construir la sentencia completa.

    jueves, 28 de julio de 2016 18:32
  • Hola Alberto. Gracias por la respuesta. Justo necesitaba una guía de por dónde enfocar el problema. Voy a estudiar lo que comentas a ver si le saco punta y lo hago funcionar. Saludos.
    jueves, 28 de julio de 2016 21:58
  • Buenas tardes.

    He probado lo que me dices y no he conseguido que me funcione como esperaba. Te pongo el código que encontré en internet y que me crea una tabla a partir de un string separado por comas:

    CREATE FUNCTION dbo.fnSplit_v2
    (
      @delimited nvarchar(max),
      @delimiter nvarchar(100)
    ) RETURNS @t TABLE
    (
    -- Id column can be commented out, not required for sql splitting string
      id int identity(1,1), -- I use this column for numbering splitted parts
      val nvarchar(max)
    )
    AS
    BEGIN
      declare @xml xml
      set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
    
      insert into @t(val)
      select
        r.value('.','varchar(max)') as item
      from @xml.nodes('//root/r') as records(r)
    
      RETURN
    END
    
    GO

    A esta función la puedo llamar así:

    SELECT* 
    FROM [dbo].[fnSplit_v2](
        'OSCAR|ESPIRILLA|FLORES'-- List of delimited items
      , '|' -- delimiter that separates items
    ) 

    Y el resultado es una tabla con los valores:

    1 OSCAR

    2 ESPIRILLA

    3 FLORES

    Si intento llamar a la función pasándole como parámetros el campo de una tabla me dice que no soporta varios registros:

    SELECT * FROM [dbo].[fnSplit_v2]( (SELECT Nombres FROM Pruebas) -- List of delimited items , ',' -- delimiter that separates items )

    Y adaptando la llamada al procedimiento:

    Select Nombres from Pruebas CROSS APPLY [dbo].[fnSplit_v2](Nombres, ',')
    

    No me devuelve error, sin embargo, el conjunto de registros devueltos no es lo que espero pues repite filas y no separa los nombres por comas. Imagino que es porque no he usado el ON al final de la sentencia y es que no se como debo usarlo. Estoy empezando con SQL y tengo muchas carencias, me atasco mucho.

    Saludos y gracias nuevamente por la ayuda.


    viernes, 29 de julio de 2016 16:46
  • El CROSS APPLY no lleva un ON, lo siento si te llevé a engaño con la respuesta anterior; se me escapó el ON porque estaba comentando que funciona igual que un JOIN (pero no se escribe igual que el JOIN, no lleva el ON).

    Al igual que el JOIN, repite los registros, es decir por cada registro de la tabla de la izquierda devuelve tantos como genere la función. Por ejemplo, si la tabla tiene 100 registros y en cada uno hay un campo de texto que tiene cuatro datos separados por comas, entonces saldrán 400 registros al hacer el cross apply (lo mismo que saldría si hicieras un join de dos tablas y la primera tuviera 100 registros y por cada uno de ellos le correspondieran 4 en la segunda tabla).

    Si solo te interesan las palabras de la segunda tabla (las que devuelve la función), no selecciones ninguna columna de la primera, pon solo el dato de la segunda, y ponle un distinct. Algo parecido a esto:

    Select distinct t2.item from Pruebas  as t1 CROSS APPLY [dbo].[fnSplit_v2](t1.Nombres, ',') as t2


    viernes, 29 de julio de 2016 18:16
  • Buenos días Alberto. Sigo agradeciendo tu interés y tu paciencia.

    He probado a montar la sentencia tal y como me indicas pero soy incapaz de hacerla funcionar eficientemente aunque los datos, ahora sí, son correctos. Si bien es cierto que la tabla origen tiene cerca de 30000 registros:

    Select distinct t2.Val from Pruebas  as t1 CROSS APPLY [dbo].[fnSplit_v2](t1.Nombres, ',') as t2

    En este caso la consulta se hace eterna, de hecho la paro cuando pasa del minuto pues ya no la considero práctica. Yo creo que es muy ineficiente este sistema para recuperar los datos tal y como lo necesito. La he dejado terminar entera y llega a 2 minutos para sacar los datos. 

    Recuperando la columna entera desde otro lenguaje de programación como VB, y manipulando los datos allí, es infinitamente más eficiente, tanto como reducir lo que tarda SQL Server en hacer esta operación de 2 minutos a 2 SEGUNDOS en VB.

    ¿Habría alguna otra solución al problema que fuera más eficiente para ejecutarla desde SQL Server?

    Saludos.


    • Editado Conrrad domingo, 31 de julio de 2016 8:48
    domingo, 31 de julio de 2016 8:32
  • Efectivamente, esto es muy ineficiente. ¿Por qué? Pues porque si la tabla tiene 30000 registros hay que llamar 30000 veces a la función, que construye 30000 tablas temporales que tienen que unirse con un JOIN a la tabla principal. A diferencia de las funciones inline, que se optimizan conjuntamente con la sentencia que las llama, las funciones multilínea de tipo tabla se ejecutan por separado cada vez que hay que evaluarlas, por lo que no resultan eficientes si hay muchos registros.

    Para que realmente corra, hay que hacerlo todo en una sola pasada, lo cual implica que el código que ahora tienes por separado dentro de la función hay que combinarlo con la sentencia que recorre la tabla completa, para que se optimice conjuntamente. Sería algo parecido a lo que te pongo a continuación, pero tendrás que pulirlo porque lo he escrito de memoria y casi seguro tendrá algún error:

    Select distinct item
    from (select r.value('.','varchar(max)') as item
     from (cast N'<root><r>' + replace(Nombres,',','</r><r>') + '</r></root>' as XML).nodes('//root/r') as records(r))
     from Pruebas

    Si no cuela todo seguido con una subconsulta tal como te lo he escrito, puedes probar a refactorizarlo usando una Common Table Expression con un WITH.

    domingo, 31 de julio de 2016 10:52
  • Gracias Alberto por la respuesta. Indagaré sobre esto. Ahora estaré unos días en OFF pero a la vuelta probaré lo que me comentas a ver si soy capaz de hacerlo funcionar. 

    Saludos.

    domingo, 31 de julio de 2016 16:37
  • Una forma de mejorar el rendimiento es usando una funcion tipo tabla en linea. La que tu creastes es tipo tabla pero de multiples sentencias.

    Otra forma seria usando otros metodos, en vez de xml, como usar una tabla de numeros.

    Chequea este hilo.

    https://social.msdn.microsoft.com/Forums/es-ES/27b6d641-f629-4654-ade8-935598a1397b/crear-procedimiento-almacenado-que-extraiga-datos-de-una-columna-separados-por-y-los-guarde-en?forum=sqlserveres


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    lunes, 1 de agosto de 2016 16:50