Trazer valores separados na coluna.

Question

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

Answer 1

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.

Answer 2

Alexandre,

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

Answer 3

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.

Answer 4

O que é campo antes da virgula e campo depois da virgula ?

Answer 5

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.

Answer 6

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'.

Answer 7

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]

Answer 8

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.

Answer 9

Ah ta entendi, então eu crio duas novas colunas na tabela e insiro no codigo.

Vou tentar

Answer 10

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.

Answer 11

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.

Answer 12

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 ?

Answer 13

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]

Answer 14

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.

Answer 15

é 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 ?

Answer 16

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.

Answer 17

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.

Answer 18

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.

Answer 19

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.

Answer 20

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]

Answer 21

Alexandre não funcionou, caramba eu refiz a tabela importei os dados pra outra tabela...fiz do zero e não quer funcionar.

Answer 22

Junior,

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

Answer 23

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.

Answer 24

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.

Answer 25

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.

Answer 26

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 ?

Answer 27

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.

Answer 28

é 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

Answer 29

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.

Answer 30

é 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.