Migration seprar address number Add-in

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
alineri
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 17
Joined: Thu, 28 Feb 2008 4:46 pm
Location: RJ

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
User avatar
madmax
Rank: DBA Pleno
Rank: DBA Pleno
Posts: 293
Joined: Wed, 13 Dec 2006 5:02 pm
Location: São Paulo
Contact:

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
alineri
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 17
Joined: Thu, 28 Feb 2008 4:46 pm
Location: RJ

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
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

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
User avatar
fsitja
Rank: OraSauro
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

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.

Select all

 
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>  
User avatar
fsitja
Rank: OraSauro
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

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.
User avatar
fsitja
Rank: OraSauro
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

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"

Select all

 
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 
User avatar
fsitja
Rank: OraSauro
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

nozes
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Thu, 23 Dec 2010 2:06 pm
Location: Mesquita-RJ
Emanuel "Nozes" Castro
Analista Programador

No comments ... I believe the guys broke!
BOS ideas!
;)

: -O
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest