search

fjtello / SQL-C-sharp

Common programming and coding tips and howtos
0 stars 0 forks source link

SQL hints [crear tabla a partir de cadena de texto] [#charindex #substring] #15

Open fjtello opened 7 years ago

fjtello commented 7 years ago

DECLARE @a AS VARCHAR(MAX) = ' 1 2 3 4 5 6;7;8 9 ';

-- Pasar datos en forma de cadena a tabla BEGIN DECLARE @separador as VARCHAR(1); SET @separador = ';'; SET @a = REPLACE(REPLACE(REPLACE(@a, CHAR(13), @separador), CHAR(10), @separador), ',', @separador);

declare @t as table (id int identity(1,1), cod varchar(50), ok tinyint);
DECLARE @inner AS VARCHAR(50); DECLARE @pos AS INT;

WHILE(CHARINDEX(@separador + @separador, @a)>0) 
    SET @a = REPLACE(@a, @separador + @separador, @separador);

WHILE(CHARINDEX(@separador, @a)>0)
BEGIN
    SET @pos = CHARINDEX(@separador, @a);   
    SET @inner = RTRIM(LTRIM(SUBSTRING(@a, 1, @pos - 1)));  
    IF(@inner <> '' AND NOT @inner LIKE '%-----%')
        INSERT INTO @t (cod) VALUES (@inner);

    SET @a = SUBSTRING(@a, @pos + 1, LEN(@a) - @pos);
END

END

SELECT * FROM @t;