none
Trazer valores separados na coluna. RRS feed

  • Pergunta

  • Boa tarde,

    Estou precisando separar os valores de uma coluna dividir o que esta antes e depois da virgula, por uma necessidade de usuários.

    Teria como fazer isso via script no banco de dados.

    AVENIDA SENADOR QUEIRÓS, 111 BOX 11
    AVENIDA SENADOR QUEIRÓS, 111
    AVENIDA SENADOR QUEIRÓS, 111
    AVENIDA SENADOR QUEIRÓS, 111 BOX 11
    AVENIDA SENADOR QUEIRÓS, 11
    AVENIDA SENADOR QUEIRÓS, 11 B
    AVENIDA SENADOR QUEIRÓS, 111

    Obrigado

    Adalberto

    quarta-feira, 30 de janeiro de 2013 14:18

Respostas

  • Fiz uma simulação com estes e não haveria problema, o que tambem pode dar o mesmo erro seriam registros que não tem a virgula.

    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Marcado como Resposta Adalvitor quarta-feira, 30 de janeiro de 2013 19:13
    quarta-feira, 30 de janeiro de 2013 19:03

Todas as Respostas

  • Adalberto, experimente como o exemplo abaixo, utilizando o charindex:

    declare @string varchar(200)
    set @string = 'AVENIDA SENADOR QUEIRÓS, 111 BOX 11'
    select LEFT(@string, CHARINDEX(',', @string, 1) -1)
    select RIGHT(@string, LEN(@string) - CHARINDEX(',', @string, 1) - 1)


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 14:47
  • Alexandre,

    É isso mesmo que estou querendo, mas como irei fazer para ele fazer isso com a coluna inteira, por que tem endereços diferentes.


    quarta-feira, 30 de janeiro de 2013 16:00
  • experimente assim:

    update tabela
    set [campo antes da virgula] = LEFT([campo da sua tabela], CHARINDEX(',', [campo da sua tabela], 1) -1),
    [campo depois da virgula] = RIGHT([campo da sua tabela], LEN([campo da sua tabela]) - CHARINDEX(',', [campo da sua tabela], 1) - 1)
    


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 16:06
  • O que é campo antes da virgula e campo depois da virgula ?
    quarta-feira, 30 de janeiro de 2013 16:13
  • a sua ideia não seria separar o campo em 2 com a parte antes e depois da virgula ?

    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 16:15
  • Isso mesmo, pra separar o logradouro do complemento.

    Mas quando executo o update alterando os locais q vc pediu, deu erro.

    Msg 207, Level 16, State 1, Line 2
    Invalid column name 'campo antes da virgula'.
    Msg 207, Level 16, State 1, Line 3
    Invalid column name 'campo depois da virgula'.

    quarta-feira, 30 de janeiro de 2013 16:21
  • Adalvitor,

    Essa é uma situação muito comum de acontecer, onde temos em uma tabela o campo de endereço totalmente fora de padrões que apresenta número, telefones, entre outros dados.

    Isso dificulta totalmente a forma de obtenção de dados, mesmo que você tente estabelecer um padrão, o que na verdade não existe.

    Em qual parte da Coluna você deseja obter esta porção de dados? Outra coisa, você vai fazer para um conjunto de valores específicos?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quarta-feira, 30 de janeiro de 2013 16:24
  • então, onde esta 'campo antes da virgula' coloque o nome do campo da sua tabela que irá as infos que estão antes da virgula e no  'campo depois da virgula' coloque o nome do campo da sua tabela que irão as informações que estão depois da virgula.

    o nome destes campos passei apenas como exemplo.


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 16:27
  • Ah ta entendi, então eu crio duas novas colunas na tabela e insiro no codigo.

    Vou tentar

    quarta-feira, 30 de janeiro de 2013 16:30
  • Sim, se sua idéia for separar em 2 colunas diferentes o endereço com o critério do antes e depois da virgula é isto mesmo.

    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 16:31
  • Olá Junior,

    Nunca tinha passado por isso, no começo até meio que conversei com o usuário e achei que não era possivel, mas ai antes de falar vim aqui ver se alguém teria alguma solução.

    Olhei a forma que as pessoas colocaram os dados e percebi que eles colocam o endereço virgula e depois o restante

    Então pensei em pegar o que esta antes da virgula e depois da virgula e separar.

    quarta-feira, 30 de janeiro de 2013 16:33
  • olha como eu coloquei

    update dbo.Plan1$
    set EnderecoA = LEFT(endereco, CHARINDEX(',', endereco, 1) -1),
    EnderecoC = RIGHT(endereco, LEN(endereco) - CHARINDEX(',', endereco, 1) - 1)

    deu esse erro

    Msg 536, Level 16, State 3, Line 1
    Invalid length parameter passed to the RIGHT function.
    The statement has been terminated.

    Fiz algo de errado ?

    quarta-feira, 30 de janeiro de 2013 16:40
  • Adalvitor,

    Então para poder identificar o que esta antes da vírgula você poderá utilizar a função CharIndex, conforme foi apresentado pelo Alexandre, da mesma forma que após a vírgula, fazendo uso além do CharIndex da Função PadIndex.

    Veja o exemplo:

    Create Table Enderecos
     (Codigo Int Identity(1,1) Primary Key,
       Descricao Varchar(200))
    
    Insert Into Enderecos 
    Values('AVENIDA SENADOR QUEIRÓS, 111 BOX 11'),
    ('AVENIDA SENADOR QUEIRÓS, 111'),
    ('AVENIDA SENADOR QUEIRÓS, 111'),
    ('AVENIDA SENADOR QUEIRÓS, 111 BOX 11'),
    ('AVENIDA SENADOR QUEIRÓS, 11'),
    ('AVENIDA SENADOR QUEIRÓS, 11 B'),
    ('AVENIDA SENADOR QUEIRÓS, 111')
    
    Select * from Enderecos
    
    Select LEFT(Descricao, CHARINDEX(',', Descricao, 1) -1) As Descricao,
               RIGHT(Descricao, LEN(Descricao) - CHARINDEX(',', Descricao, 1) - 1) As Complemento
    From Enderecos


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quarta-feira, 30 de janeiro de 2013 16:47
  • Estranho, estaria correto, o SQL esta fazendo o parse mas na hora da execução na funciona, tente desta outra maneira:

    update dbo.Plan1$
    set EnderecoA = LEFT(endereco, CHARINDEX(',', endereco, 1) -1),
    EnderecoC = SUBSTRING(endereco, CHARINDEX(',', endereco, 1) +1, LEN(endereco) - CHARINDEX(',', endereco, 1) )
    


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 16:51
  • é Alexandre, o erro persiste 

    Msg 537, Level 16, State 3, Line 1
    Invalid length parameter passed to the LEFT or SUBSTRING function.
    The statement has been terminated.

    Será que nao vai funcionar ?


    quarta-feira, 30 de janeiro de 2013 16:56
  • Faz o seguinte, roda uma parte de cada vez para vermos onde esta o erro:

    -- parte 1
    update dbo.Plan1$
    set EnderecoA = LEFT(endereco, CHARINDEX(',', endereco, 1) -1)
    -- parte 2
    update dbo.Plan1$
    set EnderecoC = SUBSTRING(endereco, CHARINDEX(',', endereco, 1) +1, LEN(endereco) - CHARINDEX(',', endereco, 1) )
    

    me fala se da erro nos 2 ou em 1 só.

    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 16:59
  • Junior, essa foi a query que adaptei.

    Select LEFT(endereco, CHARINDEX(',', endereco, 1) -1) As endereco,
           RIGHT(endereco, LEN(endereco) - CHARINDEX(',', endereco, 1) - 1) As Complemento
    From dbo.Plan1$

    Quando executo da a seguinte mensagem.

    Msg 536, Level 16, State 3, Line 1
    Invalid length parameter passed to the RIGHT function.

    quarta-feira, 30 de janeiro de 2013 17:02
  • Na 1

    Msg 537, Level 16, State 3, Line 2
    Invalid length parameter passed to the LEFT or SUBSTRING function.
    The statement has been terminated.

    E a 2 funcionou.

    A dois tava dando erro e vi o motivo era o tamanho do tipo de dados a coluna de origem tava nvarchar(255) e a de destino varchar(50), deixei pra nvarchar(255) as duas e funcionou.

    Mas o primeiro ainda deu erro.

    quarta-feira, 30 de janeiro de 2013 17:11
  • Acredito que as funções de left e right tenham alguma limitação e por isso não esta funcionando, ja que funcionou com o substring vamos adaptar a primeira situação tambem, agora o codigo abaixo deve funcionar:

    update dbo.Plan1$
    set EnderecoA = substring(endereco,1, CHARINDEX(',', endereco, 1) -1),
    EnderecoC = SUBSTRING(endereco, CHARINDEX(',', endereco, 1) +1, LEN(endereco) - CHARINDEX(',', endereco, 1) )
    

    Nos avise se funcionou e era isto mesmo o esperado.

    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 17:15
  • Adalvitor,

    Então você tem que alterar os tipos de dados e formato das suas células no Excel para que o SQL Server poss reconhecer os tipos de dados, com isso utilizar as funções Left, Right e Substring.


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    quarta-feira, 30 de janeiro de 2013 17:24
  • Alexandre não funcionou, caramba eu refiz a tabela importei os dados pra outra tabela...fiz do zero e não quer funcionar.
    quarta-feira, 30 de janeiro de 2013 17:38
  • Junior,

    eu fiz a tabela novamente e importei direto do banco para outra tabela sem usar o excel como fonte.

    quarta-feira, 30 de janeiro de 2013 17:41
  • o erro ainda continua sendo o mesmo ? tente novamente dividir em 2 partes:

    update dbo.Plan1$ set EnderecoA = substring(endereco,1, CHARINDEX(',', endereco, 1) -1)

    update dbo.Plan1$

    EnderecoC = SUBSTRING(endereco, CHARINDEX(',', endereco, 1) +1, LEN(endereco) - CHARINDEX(',', endereco, 1) )


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 17:44
  • Alexandre,

    Fiz separado e o segundo funcionou e o primeiro, continua dando esse erro e não consigo entender o que ele quer dizer.

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    quarta-feira, 30 de janeiro de 2013 18:01
  • Acho que ja descobri o que pode estar acontecendo, por acaso existem registros em branco ou nulos no seu campo endereco ? fiz uma simulação de uma valor nulo e retornou exatamente o erro que voce esta tendo, porem para a segunda situação funciona normalmente.

    tente assim eliminando registros nulos ou em branco:

    update dbo.Plan1$
    set EnderecoA = substring(endereco,1, CHARINDEX(',', endereco, 1) -1)
    where endereco is not null
    and ltrim(rtrim(endereco)) <> ''


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 18:38
  • Alexandre o erro continuou e vc tem razão e tem outra coisa que acredito que possa ser o motivo tbm.

    GERMANO DIX,
    SAPOPEMBA,
    IVO MÁRIO ISAAC PIRES,
    ISMAEL,
    ELIAS ALVES DA COSTA,

    tem endereço em banco após a virgula sem complemento, será que isso pode estar contribuindo para o erro ?


    quarta-feira, 30 de janeiro de 2013 18:59
  • Fiz uma simulação com estes e não haveria problema, o que tambem pode dar o mesmo erro seriam registros que não tem a virgula.

    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Marcado como Resposta Adalvitor quarta-feira, 30 de janeiro de 2013 19:13
    quarta-feira, 30 de janeiro de 2013 19:03
  • é acabei de consultar aqui e tem campos sem virgula.

    Então não vai funcionar né.

    Mas blz agradeço a ajuda, mesmo não dando certo aprendi algumas coisas novas.

    Obrigado

    quarta-feira, 30 de janeiro de 2013 19:13
  • Pois é, o problema de trabalhar com cadastros que vem de fora sem uma interface que tenha regras para padronizar os dados fica muito dificil de trabalhar ja que não se tem uma regra e algo que aplicamos para a maioria não atende a todos.

    Ja trabalhei em uma empresa que tinha uma ferramenta comprada de outra empresa que fazia exatamente o tratamento de dados como voce esta querendo, a ferramenta conseguia identificar sem nenhum padrao o que era rua, numero, bairro,complemento, etc por que ela possuia um banco grande de endereços nacionais e fazia diversas comparações, a performance não era muito boa mas funcionava bem para organizar este tipo de informações, se for interessante para sua empresa voce pode pesquisar e propor esta solução, infelizmente não lembro o nome para te passar mas fazendo uma busca no google não deve ser dificil de encontrar.


    Alexandre Matayosi Conde Mauricio. Se esta sugestão for útil, por favor, classifique-a como útil. Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    quarta-feira, 30 de janeiro de 2013 19:21
  • é um sistema antigo que tinha aqui na empresa, e o cadastro de endereço é tudo numa linha toda, péssima modelagem de banco ai da nisso.

    Mas vou pesquisar essa ferramenta, eu acho que não vai ser fácil achar mas vou procurar e apresentar aqui na empresa.

    Obrigado novamente.

    quarta-feira, 30 de janeiro de 2013 19:36