Skip to content
Advertisement

How do i remove duplicate address line in plsql

I have org_name, add_line1 , add_line2, add_line3 which i have put on different lines by using

(RTRIM(org_name) || CHR(32) || NVL2(RTRIM(org_name), CHR(32) || 'line' || CHR(32), '') ||
RTRIM(add_line1) || CHR(32) || NVL2(RTRIM(add_line1), CHR(32) || 'line' || CHR(32), '') ||
RTRIM(add_line2) || CHR(32) || NVL2(RTRIM(add_line2), CHR(32) || 'line' || CHR(32), '') ||
RTRIM(add_line3) || CHR(32) || NVL2(RTRIM(add_line3), CHR(32) || 'line' || CHR(32), '') || )AS "POSTAL_ADDRESS"

The problem is sometimes org_name is same as add_line1 meaning in the output I am getting the name twice on lines 1 and 2.

What I can’t figure out is how to show name once if line output is same as line2 output.

Can someone please help me on this?

enter image description here

Advertisement

Answer

a case-insensitive and having no space problem comparison might be made by using regexp_like() and trim() functions together :

case when regexp_like(trim(org_name),trim(add_line1),'i') 
     then 
          org_name 
     else add_line1 
      end as org_name 

or you may try within concatenation of POSTAL ADDRESS string as

select line_no, case when regexp_like(trim(org_name),trim(add_line1),'i') then 
           rtrim(org_name) || chr(32) || nvl2(rtrim(org_name), chr(32) || 'line' || chr(32), '') 
       else 
           rtrim(org_name) || chr(32) || nvl2(rtrim(org_name), chr(32) || 'line' || chr(32), '') ||
           rtrim(add_line1)|| chr(32) || nvl2(rtrim(add_line1), chr(32)|| 'line' || chr(32), '' 
       ) 
       end as "POSTAL_ADDRESS" 
  from tab

Demo

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement