none
Separar UM campo de string com delimitadores RRS feed

  • Pergunta

  • Preciso separar o "enésimo" campo de uma string com delimitadores, exemplo:

    @TESTE = "campo 01|campo 02|campo 03|campo 04|campo 05|campo 06||campo 08|"

    Hora preciso do primeiro, depois do terceiro, quinto... 

    Podem haver NULLs na string, valores, datas, etc. Também não há um número fixo de campos, podem haver apenas 2 ou mais.

    Interessante o uso simples da função PARSENAME, mas há uma limitação de apenas 4 valores.

    Gostaria de não recorrer a funções personalizadas ;-)

    quinta-feira, 16 de maio de 2019 12:06

Respostas

  • Segue uma versão considerando que o valor a ser separado está em uma variável:

    declare @Linha varchar(999);
    
    set @Linha = '|Coluna 1|Coluna 2|Coluna 3|Coluna 4|Coluna 5|Coluna 6|Coluna 7|Coluna 8|Coluna 9|Coluna 10|Coluna 11|';
    
    select
       (SELECT S.D.value('.', 'varchar(100)') 
        FROM c.ColunaXML.nodes('X[10]') as S(D)) as Coluna10
    from 
    (
        select cast('<X>' + replace(substring(@Linha, 2, len(@Linha) - 2), '|', '</X><X>') + '</X>' as XML) as ColunaXML
    ) as c
    

    Espero que ajude


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

    • Marcado como Resposta MLRamos sexta-feira, 24 de maio de 2019 19:45
    sexta-feira, 24 de maio de 2019 19:41

Todas as Respostas

  • Deleted
    quinta-feira, 16 de maio de 2019 12:15
  • MLRamos,

    Certo, então não temos uma posição fixa, a qualquer momento pode ser o primeiro, o Segundo, ultimo.

    Mas como você iria identificar qual valor deveria ser obtido?

    Qual é a versão do SQL Server que você esta utilizando? Se for acima da 2016 fica mais fácil te ajudar.

    A partir da versão 2016, foi adicionada a função String_Split(), talvez ela poderá te ajudar, veja abaixo alguns exemplos:

    -- Exemplo 1 – Separando de forma simples uma string –
    SELECT * 
    FROM STRING_SPLIT('Junior,Galvão,MVP,SQL Server',',')
    Go
    
    -- Exemplo 2 – Fazendo uso de variáveis como parâmetros de entrada de valores –
    DECLARE @string VARCHAR(100) = 'Microsoft,SQL Server,2016,RC0',
                    @separador CHAR(1) =','
    
    SELECT * 
    FROM STRING_SPLIT(@string,@separador)
    Go
    
    -- Exemplo 3 – Armazenando o resultado da divisão de uma string em uma nova tabela –
    DECLARE @string VARCHAR(100) = 'Microsoft,SQL Server,2016,RC0',
            @separador CHAR(1) =','
    
    SELECT * INTO #SplitTable
    FROM STRING_SPLIT(@string,@separador)
    GO
    
    -- Visualizando a estrutura da tabela --
    sp_Columns #SplitTable
    Go
    
    -- Consultando os dados da tabela --
    Select * from #SplitTable
    Go
    
    -- Exemplo 4 – Apresentando a mensagem quando o separador de string for definido com mais de um caracter --
    DECLARE @string VARCHAR(100) = 'pedrogalvaojunior#@gmail#@com',
                    @separador CHAR(2) ='#@'
    
    SELECT * FROM STRING_SPLIT(@string,@separador)
    Go
    
    -- Exemplo 5 – Apresentando o comportamento da String_Split() quando um parâmetro apresenta valor nulo –
    SELECT * FROM STRING_SPLIT('pedrogalvaojunior,wordpress,com',NULL)
    Go
    
    -- Exemplo 6 – Realizando o split de uma string com base na junção de uma tabela com a função String_Split() –
    -- Criando a tabela Split --
    Create Table Split
    ( SplitId INT IDENTITY (1,1) NOT NULL,
      SplitValue1 NVARCHAR(50), 
      SplitValue2 NVARCHAR(50))
    GO
    
    -- Inserindo linhas de registro --
    INSERT INTO Split (SplitValue1, SplitValue2)
    VALUES ('Pedro','Galvão'),
                  ('Junior','Galvão'),
                  ('Antonio','Silva'),
    			  ('Chico','Bento')
    Go
    
    -- Realizando a Junção da Tabela Split com a função Split_String() --
    Select SplitId, SplitValue1, SplitValue2, Value 
    From Split S Inner Join String_Split('Pedro,Antonio',',') STS
    				  On S.SplitValue1 = STS.Value
    Go
    
    -- Exemplo 7 – Apresentando o resultado quando ambos os parâmetros vazios –
    Select * from String_Split(' ',',')
    Go
    
    -- Exemplo 8 – Apresentando o comportamento da String_Split() quando o caracter do final da string é o mesmo utilizado como separador –
    SELECT * FROM STRING_SPLIT('Conhecendo,SQL Server,2016,',',')
    Go

    Agora se for entre as versões 2008 e 2014, acredito que o exemplo abaixo poderá lhe ajudar:

    DECLARE @var VARCHAR(100)
    SET @var = 'Conhecendo, cada, vez, mais, os segredos, do SQL Server.....' 
    
    SELECT
    	SUBSTRING	(',' + @var + ',',Number + 1,CHARINDEX	(',',',' + @var + ',',Number + 1	) - Number - 1) as value
    FROM master..spt_values
    WHERE Number >= 1
    AND Number < LEN(',' + @var + ',') - 1
    AND SUBSTRING(',' + @var + ',', Number, 1) = ','
    AND type = 'P'
    ORDER BY Number


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

    quinta-feira, 16 de maio de 2019 12:25
  • Gostaria de não recorrer a funções personalizadas

    Função nativa que faça isso, não conheço.

    Já vi funções de usuário que fazem isso: utilizam loop, pulando (n - 1) delimitadores até chegar ao enésimo elemento. Mas geralmente são funções do tipo escalar.

    A função PARSENAME realmente é de grande auxílio, mas tem a restrição que você citou: somente até 4 valores. E outra: no texto original não pode existir o caractere ".".


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Pois é... :(

    Mas com tantas novas funções como String_Split, String_Agg e "afins" não consigo chegar lá ?

    Vou citar um exemplo prático que conheço mais no Excel-VBA que também não tem essa função, mas posso pegar o campo 6 do exemplo citado acima da seguinte forma:

    Sub Macro1()
    '
    ' Macro1 Macro
    '

        Range("A1").Select
        ActiveCell.FormulaR1C1 = "campo 01|campo 02|campo 03|campo 04|campo 05|campo 06||campo 08|"
        'substitui o QUINTO pipe por $
        Range("A4").Select
        ActiveCell.FormulaR1C1 = "=SUBSTITUTE(R1C1,""|"",""$"",5)"
        'encontra a posição dele
        Range("A5").Select
        ActiveCell.FormulaR1C1 = "=SEARCH(""$"",R4C1)"
        'substitui o SEXTO pipe por $
        Range("A6").Select
        ActiveCell.FormulaR1C1 = "=SUBSTITUTE(R1C1,""|"",""$"",6)"
        'encontra a posição dele
        Range("A7").Select
        ActiveCell.FormulaR1C1 = "=SEARCH(""$"",R6C1)"
        'fórmula final com os resultados das celulas anteriores
        Range("A8").Select
        ActiveCell.FormulaR1C1 = "=MID(R1C1,R5C1+1,R7C1-R5C1-1)"
        'fórmula final "geral"
        Range("A9").Select
        ActiveCell.FormulaR1C1 = "=MID(R1C1,SEARCH(""$"",SUBSTITUTE(R1C1,""|"",""$"",5))+1,SEARCH(""$"",SUBSTITUTE(R1C1,""|"",""$"",6))-SEARCH(""$"",SUBSTITUTE(R1C1,""|"",""$"",5))-1)"
        
    End Sub


    quinta-feira, 16 de maio de 2019 12:40
  • MLRamos,

    Certo, mas em alguns cenários, não temos como não usar funções nativas, neste seu caso, não identifico no SQL Server algo chamado "native" que possa lhe ajudar.

    Então, mas qual é o critério para saber em qual momento será utilizado um ou outro valor?


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

    quinta-feira, 16 de maio de 2019 12:51
  • MLRamos,

    Certo, mas em alguns cenários, não temos como não usar funções nativas, neste seu caso, não identifico no SQL Server algo chamado "native" que possa lhe ajudar.

    Então, mas qual é o critério para saber em qual momento será utilizado um ou outro valor?


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

    Sua pergunta é no sentido de quando saber qual o "n" de enésimo valor ?

    Se sim, preciso "pegar" em alguns casos determinados campos de uma tabela com determinados TIPOS de registros. Mais fácil entender depois de ler este tópico:

    https://social.technet.microsoft.com/Forums/sqlserver/pt-BR/df9457f2-46a2-4644-aaac-6b7b52b68b3d/solues-para-seleo-dinmica-de-tabela-destino

    Grande ajuda do José Diz!

    quinta-feira, 16 de maio de 2019 13:00
  • tipo assim ... não consigo pegar "apenas" o sexto resultado (linha) de String_Split ?

    DECLARE @tags NVARCHAR(400) = 'campo 01|campo 02|campo 03|campo 04|campo 05|campo 06||campo 08|'  

    SELECT value  
    FROM STRING_SPLIT(@tags, '|') 
    =============================

    campo 01
    campo 02
    campo 03
    campo 04
    campo 05
    campo 06

    campo 08

    • Editado MLRamos quinta-feira, 16 de maio de 2019 13:07 correção
    quinta-feira, 16 de maio de 2019 13:04
  • Deleted
    • Sugerido como Resposta Junior Galvão - MVPMVP quinta-feira, 16 de maio de 2019 19:00
    • Marcado como Resposta MLRamos terça-feira, 21 de maio de 2019 16:34
    • Não Marcado como Resposta MLRamos sexta-feira, 24 de maio de 2019 17:43
    quinta-feira, 16 de maio de 2019 13:11
  • Deleted
    quinta-feira, 16 de maio de 2019 13:15
  • Mas aí você criou uma macro; não utilizou algo nativo.

    A macro em si não contém função criada, foi um erro meu deixar as linhas ali. Minha intenção foi apenas para ilustrar a parte final, onde tenho uma "fórmula composta" para chegar no resultado desejado.

    Avalie com atenção, pois pode conter erro(s).

    Ok, obrigado!

    Pedro, a função nativa string_split não pode ser utilizada, pois ela não é determínistica. Como consta na documentação, "A ordem não é a garantia de corresponder à ordem das subcadeias de caracteres na cadeia de caracteres de entrada". Inclusive já existem solicitações nesse sentido no portal de feedback: STRING_SPLIT is not feature complete.

    Campo2

    Campo5

    Campo3

    ?????? :-O

    quinta-feira, 16 de maio de 2019 13:31
  • Deleted
    quinta-feira, 16 de maio de 2019 13:36
  • realizei testes com o código da função; parece que está ok.

    Para testar a função:

    -- código #2
    SELECT dbo.EFD_n_elemento ('1111|2222|3333||5555|||8888', '|', 0),
           dbo.EFD_n_elemento ('1111|2222|3333||5555|||8888', '|', 1),
           dbo.EFD_n_elemento ('1111|2222|3333||5555|||8888', '|', 2),
           dbo.EFD_n_elemento ('1111|2222|3333||5555|||8888', '|', 3),
           dbo.EFD_n_elemento ('1111|2222|3333||5555|||8888', '|', 4),
           dbo.EFD_n_elemento ('1111|2222|3333||5555|||8888', '|', 5),
           dbo.EFD_n_elemento ('1111|2222|3333||5555|||8888', '|', 6),
           dbo.EFD_n_elemento ('1111|2222|3333||5555|||8888', '|', 7),
           dbo.EFD_n_elemento ('1111|2222|3333||5555|||8888', '|', 8),
           dbo.EFD_n_elemento ('1111|2222|3333||5555|||8888', '|', 9);
           
    SELECT dbo.EFD_n_elemento ('|', '|', 1),
           dbo.EFD_n_elemento ('|', '|', 2), 
           dbo.EFD_n_elemento ('|', '|', 3); 


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Sim ... 

    Apesar de preferir de forma diferente ... esta função atende perfeitamente !!!

    Muito Obrigado

    quinta-feira, 16 de maio de 2019 13:50
  • A partir da versão 2016, foi adicionada a função String_Split(), talvez ela poderá te ajudar, veja abaixo alguns exemplos:

    Pedro, a função nativa string_split não pode ser utilizada, pois ela não é determinística. Como consta na documentação, "A ordem não é a garantia de corresponder à ordem das subcadeias de caracteres na cadeia de caracteres de entrada". Inclusive já existem solicitações nesse sentido no portal de feedback: STRING_SPLIT is not feature complete.

    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]   [e-mail]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    José,

    Ok, obrigado.


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

    quinta-feira, 16 de maio de 2019 18:52
  • Olá José... ! Tudo bem ?
    Fiz algo mais próximo do que eu pretendia... lembra do exemplo do VBA? (não pela, macro, mas pela "fórmula composta").

    Então, fiz um "cálculo" parecido para tirar o enésimo campo da string.

    sexta-feira, 24 de maio de 2019 17:42
  • Boa tarde,

    MLRamos, não sei se já foi sugerido ou se você já fez algum teste mas segue um exemplo utilizando XML para separar um valor:

    declare @Teste table(Linha varchar(999));
    
    insert into @Teste values
    ('|Coluna 1|Coluna 2|Coluna 3|Coluna 4|Coluna 5|Coluna 6|Coluna 7|Coluna 8|Coluna 9|Coluna 10|Coluna 11|'),
    ('|Coluna A|Coluna B|Coluna C|Coluna D|Coluna E|Coluna F|Coluna G|Coluna H|Coluna I|Coluna J|Coluna K|');
    
    select
       (SELECT S.D.value('.', 'varchar(100)') 
        FROM c.ColunaXML.nodes('X[10]') as S(D)) as Coluna10
    from @Teste
    cross apply
    (
        select cast('<X>' + replace(substring(Linha, 2, len(Linha) - 2), '|', '</X><X>') + '</X>' as XML) as ColunaXML
    ) as c
    

    Espero que ajude


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

    sexta-feira, 24 de maio de 2019 18:35
  • Boa tarde,

    MLRamos, não sei se já foi sugerido ou se você já fez algum teste mas segue um exemplo utilizando XML para separar um valor:

    declare @Teste table(Linha varchar(999));
    
    insert into @Teste values
    ('|Coluna 1|Coluna 2|Coluna 3|Coluna 4|Coluna 5|Coluna 6|Coluna 7|Coluna 8|Coluna 9|Coluna 10|Coluna 11|'),
    ('|Coluna A|Coluna B|Coluna C|Coluna D|Coluna E|Coluna F|Coluna G|Coluna H|Coluna I|Coluna J|Coluna K|');
    
    select
       (SELECT S.D.value('.', 'varchar(100)') 
        FROM c.ColunaXML.nodes('X[10]') as S(D)) as Coluna10
    from @Teste
    cross apply
    (
        select cast('<X>' + replace(substring(Linha, 2, len(Linha) - 2), '|', '</X><X>') + '</X>' as XML) as ColunaXML
    ) as c

    Espero que ajude


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

    Ajudou e muito!
    Bem mais simples que minha "gambiarra", rsrs

    Esses nomes S.D.value, c.colunaXML.nodes, etc... são todos "internos"?
    Como ficaria - simplificando - para trazer apenas da linha 1 ou da linha 2 (uma qualquer escolhida) ?

    É que no seu exemplo se eu tiver 100 registros virão todas as 100 colunas 10... 
    Quero apenas da linha "X", entendeu ?


    • Editado MLRamos sexta-feira, 24 de maio de 2019 19:08 inclusão de texto
    sexta-feira, 24 de maio de 2019 18:58
  • S e D são respectivamente os nomes internos atribuídos para a tabela e a coluna retornada pelo método Nodes. Pode ser melhor utilizar nomes mais significativos. Value é o método utilizado para obter o valor do nó.

    Você pode acrescentar a clausula Where no final para que a query retorne somente os valores das linhas que deseja.

    Espero que ajude


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

    sexta-feira, 24 de maio de 2019 19:16
  • S e D são respectivamente os nomes internos atribuídos para a tabela e a coluna retornada pelo método Nodes. Pode ser melhor utilizar nomes mais significativos. Value é o método utilizado para obter o valor do nó.

    Você pode acrescentar a clausula Where no final para que a query retorne somente os valores das linhas que deseja.

    Espero que ajude


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

    Entendi...

    É que no seu exemplo funciona com um TABELA e no meu caso o resultado da pesquisa de uma tabela (a linha toda no caso) já está separada numa STRING (@LINHA).

    Vou tentar adaptar aqui, mas agradeço qualquer extra... Muito Obrigado!

    sexta-feira, 24 de maio de 2019 19:34
  • Segue uma versão considerando que o valor a ser separado está em uma variável:

    declare @Linha varchar(999);
    
    set @Linha = '|Coluna 1|Coluna 2|Coluna 3|Coluna 4|Coluna 5|Coluna 6|Coluna 7|Coluna 8|Coluna 9|Coluna 10|Coluna 11|';
    
    select
       (SELECT S.D.value('.', 'varchar(100)') 
        FROM c.ColunaXML.nodes('X[10]') as S(D)) as Coluna10
    from 
    (
        select cast('<X>' + replace(substring(@Linha, 2, len(@Linha) - 2), '|', '</X><X>') + '</X>' as XML) as ColunaXML
    ) as c
    

    Espero que ajude


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

    • Marcado como Resposta MLRamos sexta-feira, 24 de maio de 2019 19:45
    sexta-feira, 24 de maio de 2019 19:41
  • Segue uma versão considerando que o valor a ser separado está em uma variável:

    declare @Linha varchar(999);
    
    set @Linha = '|Coluna 1|Coluna 2|Coluna 3|Coluna 4|Coluna 5|Coluna 6|Coluna 7|Coluna 8|Coluna 9|Coluna 10|Coluna 11|';
    
    select
       (SELECT S.D.value('.', 'varchar(100)') 
        FROM c.ColunaXML.nodes('X[10]') as S(D)) as Coluna10
    from 
    (
        select cast('<X>' + replace(substring(@Linha, 2, len(@Linha) - 2), '|', '</X><X>') + '</X>' as XML) as ColunaXML
    ) as c
    

    Espero que ajude


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

    ajudou muuuuuuiiiiito!

    Obrigadaço!

    sexta-feira, 24 de maio de 2019 19:45
  • Deleted
    terça-feira, 28 de maio de 2019 00:10
  • Fiz algo mais próximo do que eu pretendia... lembra do exemplo do VBA? (não pela, macro, mas pela "fórmula composta").
    Então, fiz um "cálculo" parecido para tirar o enésimo campo da string.

    "MLRamos", ao ver esta sua mensagem no final de semana passado, e percebendo que você ainda estava à procura de uma solução, resolvi escrever o artigo “Obter o enésimo elemento de uma lista”. Este tópico, criado por você, foi a motivação. Inclusive o código #1 deste tópico consta lá como uma das técnicas disponíveis.

    Nos fóruns nem sempre dá para postar detalhes, por causa do espaço reduzido (comparando-se com um blog, por exemplo). Publiquei ele ontem à noite e espero que te seja útil.


    José Diz     Belo Horizonte, MG - Brasil     [T-SQL performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Opa! Muito bacana... (ainda estou lendo, rs) mas já me sinto lisonjeado ... 

    =D


    terça-feira, 28 de maio de 2019 16:15