CODEWITHSUNDEEP
sqloracle-database
K124
K124

Reputation: 23

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

Upvotes: 2

Views: 285

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

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

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143023

Using CASE (or DECODE), e.g.

case when org_name <> add_line1 then <value you want to return)
     else null
end

Upvotes: 0

Related Questions