Hi all,
with
data as (
select 'Mary America.1234 Southridge Park Dr..Samna Lorie.MO.31234..' val from dual
union all
select 'Bill Johnson.Apartment 3B.Sterling.VA.20166.3 Falke Plaza.' from dual
)
select
regexp_substr(shipping_instructions, '[^#]+', 1, 1) name
,regexp_substr(shipping_instructions, '[^#]+', 1, 2) address
,regexp_substr(shipping_instructions, '[^#]+', 1, 3) city
,regexp_substr(shipping_instructions, '[^#]+', 1, 4) state
,regexp_substr(shipping_instructions, '[^#]+', 1, 5) zip
,regexp_substr(shipping_instructions, '[^#]+', 1, 6) county
FROM (select REGEXP_REPLACE (val, '\.([^ .])', '#\1') shipping_instructions from data)
1)'Mary America.1234 Southridge Park Dr..Samna Lorie.MO.31234..'
2)'Bill Johnson.Apartment 3B.Sterling.VA.20166.3 Falke Plaza.'
'Mary America.1234 Southridge Park Dr..Samna Lorie.MO.31234..'
NAME ADDRESS CITY SATE ZIP county
--------- -------- ------------ ------ ------- -- -----
Mary America 1234 Southridge Park Dr. Samna Lorie MO 31234.. NULL
'Bill Johnson.Apartment 3B.Sterling.VA.20166.3 Falke Plaza..'
NAME ADDRESS CITY SATE ZIP county
---------------- ----------------- ------- ----- --- --------
Bill Johnson Apartment 3B Sterling VA 20166 3 Falke Plaza.
Problem's
---------
1)if the value, if not the county(if null) value then it shows the ZIP Value with dot's(..), in the above 31234..,
how to resolve this issue?
2)is there any another way to resolve the code, i mean use substr and instr.
3)which one is the best approach?
Conditions:
--------------
1)shipping_instructions column has value with "Mary America.1234 Southridge Park Dr..Samna Lorie.MO.31234.."
that means it combined all the like name,address,city,state,zip
2) we insreted into the table separately,
=========================
/*Using Substr and Instr*/
=========================
I used with substr and instr , but i am getting the below results(without Dot in the Address),
'Mary America.1234 Southridge Park Dr..Samna Lorie.MO.31234..'
NAME ADDRESS CITY SATE ZIP county
--------- -------- ------------ ------ ------- -- -----
Mary America 1234 Southridge Park Dr Samna Lorie MO 31234.. NULL
But i need the below results(with Dot in the Address)
NAME ADDRESS CITY SATE ZIP county
--------- -------- ------------ ------ ------- -- -----
Mary America 1234 Southridge Park Dr. Samna Lorie MO 31234.. NULL