0

I have a recursive query as below:

with addresses as

(

  select cust_id,address addr from

  (

  select 10 cust_id,'9 Help Street, Level 4' address from dual union all

  select 11 cust_id,'22 Victoria Street' address from dual union all

  select 12 cust_id,'1495 Franklin Str.' address from dual union all

  select 13 cust_id,'30 Hasivim St.,Petah-Tikva' address from dual union all

  select 14 cust_id,'2 Jakaranda St' address from dual union all

  select 15 cust_id,'61, Science Park Rd' address from dual union all

  select 16 cust_id,'61, Social park road.' address from dual union all

  select 17 cust_id,'Av. Hermanos Escobar 5756' address from dual union all

  select 18 cust_id,'Ave. Hermanos Escobar 5756' address from dual union all

  select 19 cust_id,'8000 W FLORISSANT Ave.' address from dual union all

  select 20 cust_id,'8600 MEMORIAL PKWY SW' address from dual union all

  select 21 cust_id,'8200 FLORISSANTMEMORIALWAYABOVE SW' address from dual union all

  select 22 cust_id,'8600 MEMORIALFLORISSANT PKWY SW.' address from dual

  ) t1

),

 replacements as

  (

  select id,to_str,from_string from_str from

  (

  select 1 id,'St' to_str,'Street' from_string from dual union all

 select 2 id,'St' to_str,'St' from_string from dual union all

 select 3 id,'St' to_str,'Strit' from_string from dual union all

 select 4 id,'St' to_str,'Str' from_string from dual union all

 select 5 id,'Rd' to_str,'Rd.' from_string from dual union all

 select 6 id,'Rd' to_str,'road' from_string from dual union all

 select 7 id,'Av' to_str,'Av.' from_string from dual union all

 select 8 id,'Av' to_str,'Ave' from_string from dual union all

 select 9 id,'Av' to_str,'Avenue' from_string from dual union all

 select 10 id,'Av' to_str,'Aven.' from_string from dual union all

 select 11 id,'West' to_str,'W' from_string from dual union all

 select 12 id,'South West' to_str,'SW' from_string from dual

 ) t2

),

 r(cust_id,addr,test_addr,l) as 

 (

   select cust_id,addr,regexp_replace(addr,'(^|\W)' || from_str || '(\W|$)','\1' || to_str || '\2') test_addr,

     id - 1

     from  

     addresses,

     replacements

     where id = (select count(*) from replacements)

   union all

     select cust_id,addr,regexp_replace(test_addr,'(^|\W)' || from_str || '(\W|$)','\1' || to_str || '\2') test_addr,

     l - 1

     from r,

     replacements

     where id = l

  )

 select cust_id,addr,test_addr,l

 from r

 where l=0

 ;

present output:

enter image description here

Query not working as expected for cust_id like 16,18,22. in cust_id dot is there after road but still it changes to Rd.

I need 2 queries...one with exact match including dot and the other sql having match with dot or without dot.

The expect out put for first sql with exact match: enter image description here

The expected output with second sql with or without dot: enter image description here

Thanks

Thanks,

1 Answer 1

1

What about cust_id 12 in expected output of exact match? Is that expected? Did you try \s instead of \W ?

Sign up to request clarification or add additional context in comments.

10 Comments

Yes, you are right...it was my mistake...it should be Str. in addr and in test_addr as well. 12 1495 Franklin Str. 1495 Franklin Str.
I tried with /s and it works if the replacement word is at starting or end of the string. if it is in the middle of the string, it doesn't work. for example: 10 9 Help Street, Level 4 9 Help Street, Level 4....can you take a look?
Will inserting another row in replacements help? select 13 id,'St' to_str,'Street,' from_string from dual
But I can't insert all combinations into the replacements table. I just go with what is provided by the customer but output is handled in sql.
so dot character is the problem. Please let me know if this statement is right ? "Any word in the text with dot at the end should not be replaced others can be replaced". Please try with regexp like below to see if this is the expected output. code regexp_replace(addr,'(^|\s)' || from_str || '([\,]\s|$)','\1' || to_str || '\2') code This requires to mention all the possible characters next to comma. I dont know whether this is the right way. just an attempt. [Fiddle Reference]: (dbfiddle.uk/…)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.