Migration seprar address number Add-in

[alineri]

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

[madmax]

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]

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]

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

[fsitja]

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>  

[fsitja]

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]

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 

[fsitja]

Reference documentation:
http://download.oracle.com/docs/cd/E118 ... #ADFNS1003

[nozes]

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


: -O