none
Problema com campo varchar RRS feed

  • Pergunta

  • Boa tarde

    Estou tentando montar uma query que consiga dividir em linhas os retornos de um campo varchar.
    A aplicação grava nele da seguinte forma ALÇA@CAIXA DE EMBREAGEM@CARDAM@MOLA DE TRAÇÃO@TAMBOR DA EMBREAGEM@TUBO@.
    O @ é o divisor dos resultados, e cada resultado retornaria em uma linha do resultado.

    Já tentei quebrar as linhas com CHAR(13) e CHAR (10), mas preciso que voltem em outra linhas esse resultado não na mesma só com quebra de linha.

    Alguém consegue me dar uma ideia de como fazer ?

    terça-feira, 7 de julho de 2020 20:13

Todas as Respostas

  • Boa tarde,

    Qual versão do SQL Server você está utilizando?

    A partir da versão 2016 você pode utilizar a função nativa String_Split conforme o exemplo abaixo:

    select
        ca.Valores
    from Tabela as t
    cross apply
    (
      select value as Valores from STRING_SPLIT(t.ColunaVarChar, '@') 
    ) as ca

    Espero que ajude


    Assinatura: http://www.imoveisemexposicao.com.br

    • Sugerido como Resposta Avatar SQL segunda-feira, 13 de julho de 2020 11:32
    terça-feira, 7 de julho de 2020 20:42
  • Caro Colega,

    Caso você esteja utilizando versões anteriores a 2016, talvez estes outros exemplos podem lhe ajudar:

    -- Separando valores em múltiplas colunas --
    CREATE FUNCTION dbo.UFN_SEPARATES_COLUMNS(@TEXT1 varchar(8000), @COLUMN tinyint, @SEPARATOR char(1))
    RETURNS varchar(8000)
    AS
      BEGIN
           DECLARE @POS_START  int = 1
           DECLARE @POS_END    int = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
    
           WHILE (@COLUMN >1 AND @POS_END> 0)
             BEGIN
                 SET @POS_START = @POS_END + 1
                 SET @POS_END = CHARINDEX(@SEPARATOR, @TEXT, @POS_START)
                 SET @COLUMN = @COLUMN - 1
             END 
    
           IF @COLUMN > 1  SET @POS_START = LEN(@TEXT) + 1
           IF @POS_END = 0 SET @POS_END = LEN(@TEXT) + 1 
    
           RETURN SUBSTRING (@TEXT, @POS_START, @POS_END - @POS_START)
      END
    GO
    
    SELECT
      dbo.UFN_SEPARATES_COLUMNS(@ISBN, 1, '-') AS PREFIX,
      dbo.UFN_SEPARATES_COLUMNS(@ISBN, 2, '-') AS REGISTRATION_GROUP,
      dbo.UFN_SEPARATES_COLUMNS(@ISBN, 3, '-') AS REGISTRANT,
      dbo.UFN_SEPARATES_COLUMNS(@ISBN, 4, '-') AS PUBLICATION,
      dbo.UFN_SEPARATES_COLUMNS(@ISBN, 5, '-') AS [CHECK]
    GO

    Create FUNCTION [dbo].[fnSplitString] 
    ( 
        @string NVARCHAR(MAX), 
        @delimiter CHAR(1) 
    ) 
    RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
    ) 
    BEGIN 
        DECLARE @start INT, @end INT 
        SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
        WHILE @start < LEN(@string) + 1 BEGIN 
            IF @end = 0  
                SET @end = LEN(@string) + 1
           
            INSERT INTO @output (splitdata)  
            VALUES(SUBSTRING(@string, @start, @end - @start)) 
            SET @start = @end + 1 
            SET @end = CHARINDEX(@delimiter, @string, @start)
            
        END 
        RETURN 
    END
    
    select * from dbo.fnSplitString('Querying SQL Server','')
    
    
    CREATE TABLE tabela (ID int, VALOR varchar(200));
    INSERT into tabela values 
      (1111,'A1, A2, A3  '), (2222,'B1'), (3333, ' C1,  C2,  C3,C4');
    
    CREATE TABLE nova_tabela (ID int, VALOR varchar(30));
    go
    
    -- cria tabela auxiliar de números
    set NoCount on;
    
    declare @Nums table (N int);
    declare @I int;
    
    set @I= 0;
    
    while @I <= 200
     begin
      INSERT into @Nums values (@I);
      set @I+= 1;
     end;
    
    
    --INSERT into nova_tabela (ID, VALOR)
      SELECT T1.ID, ltrim(rtrim(SubString(T1.VALOR, N, CharIndex(',', T1.VALOR + ',', N) - N)))
        from tabela as T1  inner join  @Nums 
             on N <= DataLength(T1.VALOR) + 1
                and SubString(',' + T1.VALOR, N, 1) = ',';

    -- Separando valores por vírgulas --
    CREATE FUNCTION SplitID
    (
      @IDString VARCHAR(1000)
    )
    RETURNS @IDs TABLE
    (ID INT)
    AS
    BEGIN
     DECLARE @Position int
     WHILE len(@IDString) > 0
       BEGIN
         SET @Position = charindex(',', @IDString)
         IF @Position > 0
           begiN
             INSERT @IDs
             SELECT CONVERT(INT, LEFT(@IDString, @Position - 1))
             SET @IDString = RIGHT(@IDString, LEN(@IDString) - @Position)
           END
         ELSE
           BEGIN
             INSERT @IDs
             SELECT CONVERT(INT, @IDString)
             SET @IDString = ''
           END
       END
    RETURN
    END 
    
    
     DECLARE @EmpresaIDs VARCHAR(2000)
    SET @EmpresaIDs = '1, 2, 3, 4, 5'
    SELECT E.*
    FROM Empresas E
    INNER JOIN SplitIDString(@EmpresaIDs) I
    ON E.Empresa = I.ID 

    -- Utilizando CTE e Aplicando o conceito de Array para separar valores --
    Set NoCount On;
    
    Use tempdb;
    
    If OBJECT_ID('dbo.Numeracao','U') Is Not Null
     Drop Table dbo.Numeracao;
    
    Create Table dbo.Numeracao
    (Numero Int Not Null Primary Key) ;
    Go
    
    Declare @ValorMaximo Int, 
                    @Contador Int;
                    
    Set @ValorMaximo=1000000;
    Set @Contador=1;
    
    Insert Into dbo.Numeracao Values(1)    
    
    While @Contador * 2 <= @ValorMaximo
     Begin
     
      Insert Into dbo.Numeracao
       Select Numero+@Contador from dbo.Numeracao;
        
      Set @Contador *= 2;        
     End 
    
    Insert Into dbo.Numeracao
     Select Numero + @Contador from dbo.Numeracao
     Where Numero + @Contador <= @ValorMaximo
    Go
    
    IF OBJECT_ID('dbo.Arrays') Is Not Null
     Drop Table dbo.Arrays;
    
    Create Table dbo.Arrays
    (Idx Varchar(10) Not Null Primary Key,
      Elementos Varchar(8000) Not Null)
    Go
    
    Insert Into Arrays(Idx, Elementos) 
    Values 
     ('A','20,223,2544,25567,14'),
     ('B','30,-23433,28'),
     ('C','12,10,8099,12,1200,13,12,14,10,9'),
     ('D','-4,-6,-45678,-2') 
    
    Select A.Idx, A.Elementos, 
                N.Numero
    from dbo.Arrays A Join dbo.Numeracao N
                                      On N.Numero <= DATALENGTH(A.Elementos)
                                      And SUBSTRING (Elementos, Numero, 1) = ','                        
                                      
    Select A.Idx, 
               Substring(A.Elementos, N.Numero,  
                CHARINDEX(',', A.Elementos + ',', N.Numero)-N.Numero) As Elementos            
    from dbo.Arrays A Join dbo.Numeracao N
                                      On N.Numero <= DATALENGTH(A.Elementos) + 1
                                      And SUBSTRING (',' + A.Elementos, Numero, 1) = ','
                                      
     Select ROW_NUMBER() Over(Partition By A.Idx Order By N.Numero) As Posição,
               A.Idx, 
              Substring(A.Elementos, N.Numero, CHARINDEX(',', A.Elementos +',', N.Numero)-N.Numero) As Elementos            
    from dbo.Arrays A Join dbo.Numeracao N
                                      On N.Numero <= DATALENGTH(A.Elementos) 
                                      And SUBSTRING (','+A.Elementos, Numero, 1) = ','                                 
    
    With Split As
    (
     Select Idx, 1 As Pos, 1 As PosIni,
                CharIndex(',',Elementos + ',')  - 1 As PosFim
     From dbo.Arrays
     Where DATALENGTH(Elementos) > 0
    
     Union All
    
     Select Spl.Idx, Spl.Pos+1, Spl.PosFim + 2,
                CHARINDEX(',', A.Elementos + ',', Spl.PosFim + 2) - 1
     From Split As Spl Inner Join dbo.Arrays A
                                  On A.Idx = Spl.Idx
                                  And CHARINDEX(',', A.Elementos + ',', Spl.PosFim + 2) > 0
    )
    Select A.Idx, Spl.Pos,
               CAST(SUBSTRING(A.Elementos, Spl.PosIni, Spl.PosFim-Spl.PosIni+1) As Int) As Elementos
    From dbo.Arrays A Join Split As Spl
                                    On spl.Idx = A.Idx
    Order By A.Idx, Spl.Pos; 
    
    -- Construindo a Estrutura Completa do Array --
    Create Table dbo.ArrayCompleto
    (Idx Int Identity Primary Key,
     Identificador Char(1) Not Null,
     Elementos Varchar(10))
    
    -- Inserindo os dados na Tabela ArrayCompleto --   
    Insert Into ArrayCompleto (Identificador, Elementos)
    Select A.Idx, 
               Substring(A.Elementos, N.Numero,  
                CHARINDEX(',', A.Elementos + ',', N.Numero)-N.Numero) As Elementos
    
    from dbo.Arrays A Join dbo.Numeracao N
                                      On N.Numero <= DATALENGTH(A.Elementos) + 1
                                      And SUBSTRING (',' + A.Elementos, Numero, 1) = ','
    Go
    
    Select * from ArrayCompleto
    Go



    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 7 de julho de 2020 21:48