Hello,
I am making a migration of an old client system, in which an address field was created and in it were registered
Address number and add-on. Without an exact separation standard between them.
and for this migration I need to separate this data into specific fields.
Anyone any idea?
ATT,
Alineri
Migration seprar address number Add-in
Hi Aline.
You can use the Translate function according to this discussion here in the forum:
http://en.glufke.net/oracle/viewtopic.php?t=1397
I hope I have helped.
Douglas - Madmax
You can use the Translate function according to this discussion here in the forum:
http://en.glufke.net/oracle/viewtopic.php?t=1397
I hope I have helped.
Douglas - Madmax
Our face,
has already helped a 95%
very legal this function
the problem and that I have this situation
May First of May, 23 - Room 2
Applying the function, it is like this:
232
I needed M only 23
anyway thank you very much for the tip
has already helped a 95%
very legal this function
the problem and that I have this situation
May First of May, 23 - Room 2
Applying the function, it is like this:
232
I needed M only 23
anyway thank you very much for the tip
-
- Moderador
- Posts: 1396
- Joined: Fri, 01 Feb 2008 2:06 pm
- Location: Rio de Janeiro - RJ
- Contact:
will be inevitable to study some cases ...
and if you create a Java Store procedure
LA you will have access to a method called tokenizer that divides a string in excerpts according to a carcter that you pass by parameter ..
or use the charat that returns the position of a char and then you can make a substring .. in the end you will have a greater range of resources Migrate your field Address
is an idea
and if you create a Java Store procedure
LA you will have access to a method called tokenizer that divides a string in excerpts according to a carcter that you pass by parameter ..
or use the charat that returns the position of a char and then you can make a substring .. in the end you will have a greater range of resources Migrate your field Address
is an idea
- fsitja
- Rank: OraSauro
- Posts: 611
- Joined: Mon, 19 Jan 2009 4:29 pm
- Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html
OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html
OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist
The quietest way to solve this is with regular expressions. There are several exception cases that you will have to improve in regular expression, it is a very powerful tool and you can do everything.
SQL> with t as (
2 select 'AV SERTORIO, 6600' endereco from dual
3 union select 'AV SERTORIO, 6600 SOBRELOJA' from dual
4 union select 'RUA BOM JESUS, 159' from dual
5 union select 'RUA SERGIO DIETRICH, S/N' from dual
6 union select 'AV PARAGUACU, 2578' from dual
7 union select 'RUA BARAO DO RIO BRANCO 159' from dual
8 union select 'AV. PERIMETRAL Nº 12 apt. 202' from dual
9 union select 'AV. PERIMETRAL (Nº) 12 apt. 202' from dual)
10 select regexp_substr(t.endereco, '^[^,-]+', 1, 1, 'i') logradouro,
11 regexp_replace(substr(t.endereco, 1, regexp_instr(
12 t.endereco, '\d+', 1, 1, 0, 'i') - 1), '[,]\s$', '') logradouro2,
13 regexp_substr(t.endereco, '\d+', 1, 1, 'i') numero,
14 ltrim(regexp_substr(t.endereco, '.+',
15 decode(regexp_instr(t.endereco, '\d+', 1, 1, 1, 'i'),
16 0,
17 length(t.endereco)+1,
18 regexp_instr(t.endereco, '\d+', 1, 1, 1, 'i'))
19 , 1, 'i')) complemento
20 from t
21 /
LOGRADOURO LOGRADOURO2 NUMERO COMPLEMENT
------------------------------- ------------------------------ ------ ----------
AV PARAGUACU AV PARAGUACU 2578
AV SERTORIO AV SERTORIO 6600
AV SERTORIO AV SERTORIO 6600 SOBRELOJA
AV. PERIMETRAL (Nº) 12 apt. 202 AV. PERIMETRAL (Nº) 12 apt. 202
AV. PERIMETRAL Nº 12 apt. 202 AV. PERIMETRAL Nº 12 apt. 202
RUA BARAO DO RIO BRANCO 159 RUA BARAO DO RIO BRANCO 159
RUA BOM JESUS RUA BOM JESUS 159
RUA SERGIO DIETRICH
8 linhas selecionadas.
SQL>
- fsitja
- Rank: OraSauro
- Posts: 611
- Joined: Mon, 19 Jan 2009 4:29 pm
- Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html
OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html
OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist
As you can see, expressions such as "no" or "number" written by extensive will require more timely treatment, but you can embed in regular expression. It will only swell SQL a little but it's the way in my opinion.
- fsitja
- Rank: OraSauro
- Posts: 611
- Joined: Mon, 19 Jan 2009 4:29 pm
- Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html
OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html
OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist
I noticed a mistake after I posted, when it does not have the line the line was not returning, and I moved to change its test case that was posted above, from the "room 2"
with t as (
select 'AV SERTORIO, 6600' endereco from dual
union select 'AV SERTORIO, 6600 SOBRELOJA' from dual
union select 'RUA BOM JESUS, 159' from dual
union select 'RUA SERGIO DIETRICH, S/N' from dual
union select 'AV PARAGUACU, 2578' from dual
union select 'RUA BARAO DO RIO BRANCO 159' from dual
union select 'AV. PERIMETRAL Nº 12 apt. 202' from dual
union select 'AV. PERIMETRAL (Nº) 12 apt. 202' from dual
union select 'Rua Primeiro de Maio, 23 - Sala 2' from dual)
------------------ início da query ------------------------
select t.endereco,
upper(regexp_replace(substr(t.endereco, 1,
decode(regexp_instr(t.endereco, '\d+', 1, 1, 0, 'i'),
0, length(t.endereco) + 1,
regexp_instr(t.endereco, '\d+', 1, 1, 0, 'i')) - 1),
'[,]\s$', '')) logradouro,
regexp_substr(t.endereco, '\d+', 1, 1, 'i') numero,
upper(regexp_replace(regexp_substr(t.endereco, '.+',
decode(regexp_instr(t.endereco, '\d+', 1, 1, 1, 'i'),
0,
length(t.endereco)+1,
regexp_instr(t.endereco, '\d+', 1, 1, 1, 'i'))
, 1, 'i'), '^[- ,—/]*', '')) complemento
from t
- fsitja
- Rank: OraSauro
- Posts: 611
- Joined: Mon, 19 Jan 2009 4:29 pm
- Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html
OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html
OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist
-
- Information
-
Who is online
Users browsing this forum: No registered users and 1 guest