none
Crear procedimiento almacenado que extraiga datos de una columna separados por ; y los guarde en otra tabla RRS feed

  • Pregunta

  • Buenas tardes, deseo extraer datos de una tabla la cual tiene diferentes columnas y de una de ellas necesito extraer 

    datos como por ejemplo  ;#ASTM D1230;#AZO DYES;#BS 5852;# y escogerlos por la separacion del ; y enviarlos a otra tabla.

    Creo lo puedo hacer por medio de un procedimiento almacenado, pero no lo tengo claro.

     Muchas gracias quien me pueda ayudar.

    

    martes, 28 de junio de 2016 19:23

Respuestas

  • Aca un ejemplo.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Itzik's VATN
    CREATE FUNCTION [dbo].[ufn_GetNums](@low AS bigint, @high AS bigint) 
    RETURNS table
    AS
    RETURN (
    WITH
    L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5)
    SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
    FROM Nums
    ORDER BY rownum
    );
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- Erland's inline_split_me
    CREATE FUNCTION [dbo].[ufn_inline_split_me](@param nvarchar(MAX), @delimiter nchar(1) = N',')
    RETURNS table AS
    RETURN (
    SELECT
        ROW_NUMBER() OVER(ORDER BY n) AS pos,
        LTRIM(RTRIM(CONVERT(nvarchar(4000),
            SUBSTRING(@param, n,
                CHARINDEX(@delimiter COLLATE Slovenian_BIN2, @param + CONVERT(nvarchar(MAX), @delimiter), n) - n)))) AS Value
    FROM
        dbo.ufn_GetNums(1, LEN(@param))
    WHERE
        SUBSTRING(CONVERT(nvarchar(MAX), @delimiter) + @param, n, 1) = @delimiter COLLATE Slovenian_BIN2
    );
    GO
    DECLARE @T table (
    col1 int NOT NULL IDENTITY PRIMARY KEY,
    col2 varchar(128)
    );
    
    INSERT INTO @T (col2)
    VALUES
        (';#ASTM D1230;#AZO DYES;#BS 5852;#');
    
    SELECT 
        T.col1,
        T.col2,
        U.pos,
        U.Value 
    FROM
        @T AS T
        CROSS APPLY
        dbo.ufn_inline_split_me(T.col2, N';') AS U
    GO

    Puedes pivotear luego para transponer filas a columnas pero si el numero de valores es fijo entonces tambien puedes usar las funciones de cadena como SUBSTRING, CHARINDEX, PATINDEX, etc.

    SELECT 
        P.*
    FROM
        @T AS T
        CROSS APPLY
        dbo.ufn_inline_split_me(T.col2, N';') AS U
        PIVOT
        (
        MAX(U.value)
        FOR U.pos IN ([1], [2], [3], [4] ,[5])
        ) AS P;
    GO


    AMB

    Some guidelines for posting questions...

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


    martes, 28 de junio de 2016 20:05