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?
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