OraFAQ Forum: SQL & PL/SQL » Concatenating address fields

Home » SQL & PL/SQL » SQL & PL/SQL » Concatenating address fields (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Concatenating address fields [message #599705]

Mon, 28 October 2013 05:54

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

HI All,

The pipe separator needs to appear only when values found in addr1 or addr2 or addr3.

WITH address AS (SELECT 'Silver Arc Plaza,' addr1,'4th Floor, 20/1, New Palasia' addr2,'Indore' addr3 FROM dual UNION ALL
                  SELECT 'Shop No. 1,Vishnu Priya Building,' addr1,'' addr2,'pune' addr3 FROM dual UNION ALL
                  SELECT 'D/7, Siddhivinayak Nagari,' addr1,'Nr. Majura Gate, Ghod   dod Road,' addr2,'' addr3 FROM dual UNION ALL
                  SELECT '' addr1,'B 4, Gold Coin Complex,' addr2,'Ahmedabad' addr3 FROM dual UNION ALL
                  SELECT '' addr1,'' addr2,'' addr3 FROM dual)
select addr1||'|'||addr2||'|'||addr3 address from address;

Regards,
Nathan

[Updated on: Mon, 28 October 2013 05:55]

Report message to a moderator

Re: Concatenating address fields [message #599708 is a reply to message #599705]

Mon, 28 October 2013 05:57

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Use DECODE.

Report message to a moderator

Re: Concatenating address fields [message #599710 is a reply to message #599705]

Mon, 28 October 2013 05:58

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Use DECODE or CASE.

Report message to a moderator

Re: Concatenating address fields [message #599711 is a reply to message #599710]

Mon, 28 October 2013 05:59

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Actually I'd use rtrim in this case.

Report message to a moderator

Re: Concatenating address fields [message #599713 is a reply to message #599711]

Mon, 28 October 2013 06:01

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, good idea, easier to read.
Maybe a problem if you can have addr3 without addr2.

Report message to a moderator

Re: Concatenating address fields [message #599714 is a reply to message #599713]

Mon, 28 October 2013 06:09

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

It looks like you can have addys missing in any location.
I'd probably use CASE personally, but you could use an LTRIM/RTRIM/REPLACE combo if you wanted...

Report message to a moderator

Re: Concatenating address fields [message #599715 is a reply to message #599705]

Mon, 28 October 2013 06:13

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

NVL2

SQL> WITH address AS (SELECT 'Silver Arc Plaza,' addr1,'4th Floor, 20/1, New Palasia' addr2,'Indore' addr3 FROM dual UNION ALL
  2                    SELECT 'Shop No. 1,Vishnu Priya Building,' addr1,'' addr2,'pune' addr3 FROM dual UNION ALL
  3                    SELECT 'D/7, Siddhivinayak Nagari,' addr1,'Nr. Majura Gate, Ghod   dod Road,' addr2,'' addr3 FROM dual UNION ALL
  4                    SELECT '' addr1,'B 4, Gold Coin Complex,' addr2,'Ahmedabad' addr3 FROM dual UNION ALL
  5                    SELECT '' addr1,'' addr2,'' addr3 FROM dual)
  6  SELECT NVL2(ADDR1,  ADDR1 || ' | ', ADDR1)||NVL2(ADDR2,  ADDR2 || ' | ', ADDR2)||ADDR3 ADDRESS
  7    FROM ADDRESS;
 
ADDRESS
--------------------------------------------------------------------------------
Silver Arc Plaza, | 4th Floor, 20/1, New Palasia | Indore
Shop No. 1,Vishnu Priya Building, | pune
D/7, Siddhivinayak Nagari, | Nr. Majura Gate, Ghod   dod Road, |
B 4, Gold Coin Complex, | Ahmedabad

Report message to a moderator

Re: Concatenating address fields [message #599716 is a reply to message #599714]

Mon, 28 October 2013 06:15

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

That'd be 7 combos for the case, I'd probably go with LTRIM/RTRIM/REPLACE and comments to explain what it's doing.

Report message to a moderator

Re: Concatenating address fields [message #599717 is a reply to message #599715]

Mon, 28 October 2013 06:18

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Lalit Kumar B wrote on Mon, 28 October 2013 11:13

NVL2

Almost, you've got a trailing | on one line. An rtrim will fix it:

WITH address AS (SELECT 'Silver Arc Plaza,' addr1,'4th Floor, 20/1, New Palasia' addr2,'Indore' addr3 FROM dual UNION ALL
                  SELECT 'Shop No. 1,Vishnu Priya Building,' addr1,'' addr2,'pune' addr3 FROM dual UNION ALL
                  SELECT 'D/7, Siddhivinayak Nagari,' addr1,'Nr. Majura Gate, Ghod   dod Road,' addr2,'' addr3 FROM dual UNION ALL
                  SELECT '' addr1,'B 4, Gold Coin Complex,' addr2,'Ahmedabad' addr3 FROM dual UNION ALL
                  SELECT '' addr1,'' addr2,'' addr3 FROM dual)
SELECT rtrim((NVL2(ADDR1,  ADDR1 || '|', ADDR1)||NVL2(ADDR2,  ADDR2 || '|', ADDR2)||ADDR3), '|') ADDRESS
from address;

Report message to a moderator

Re: Concatenating address fields [message #599719 is a reply to message #599716]

Mon, 28 October 2013 06:34

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Fair comment CM.

PS, thanks for the Grill in the Alley suggestion. Perfect!

Report message to a moderator

Re: Concatenating address fields [message #599723 is a reply to message #599719]

Mon, 28 October 2013 06:50

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Thank you all guys. It's worked perfectly.

Report message to a moderator

Re: Concatenating address fields [message #599737 is a reply to message #599717]

Mon, 28 October 2013 07:39

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

case or decode would look more complex, ltrim, rtrim and replace is about the same as yours:

WITH address AS (SELECT 'Silver Arc Plaza,' addr1,'4th Floor, 20/1, New Palasia' addr2,'Indore' addr3 FROM dual UNION ALL
                  SELECT 'Shop No. 1,Vishnu Priya Building,' addr1,'' addr2,'pune' addr3 FROM dual UNION ALL
                  SELECT 'D/7, Siddhivinayak Nagari,' addr1,'Nr. Majura Gate, Ghod   dod Road,' addr2,'' addr3 FROM dual UNION ALL
                  SELECT '' addr1,'B 4, Gold Coin Complex,' addr2,'Ahmedabad' addr3 FROM dual UNION ALL
                  SELECT '' addr1,'' addr2,'' addr3 FROM dual)
select replace(rtrim(ltrim(addr1||'|'||addr2||'|'||addr3, '|'), '|'), '|') address from address;

Report message to a moderator

Re: Concatenating address fields [message #599814 is a reply to message #599737]

Tue, 29 October 2013 04:14

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

It is replacing all intermediate "|" as well which should not happen. So message #599717 solution is perfectly fine for the requirement.

Report message to a moderator

Re: Concatenating address fields [message #599815 is a reply to message #599814]

Tue, 29 October 2013 04:17

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

That was a mistake in my sql, should have been:

WITH address AS (SELECT 'Silver Arc Plaza,' addr1,'4th Floor, 20/1, New Palasia' addr2,'Indore' addr3 FROM dual UNION ALL
                  SELECT 'Shop No. 1,Vishnu Priya Building,' addr1,'' addr2,'pune' addr3 FROM dual UNION ALL
                  SELECT 'D/7, Siddhivinayak Nagari,' addr1,'Nr. Majura Gate, Ghod   dod Road,' addr2,'' addr3 FROM dual UNION ALL
                  SELECT '' addr1,'B 4, Gold Coin Complex,' addr2,'Ahmedabad' addr3 FROM dual UNION ALL
                  SELECT '' addr1,'' addr2,'' addr3 FROM dual)
select replace(rtrim(ltrim(addr1||'|'||addr2||'|'||addr3, '|'), '|'), '||') address from address;

two || for the replace.

Report message to a moderator

Re: Concatenating address fields [message #599817 is a reply to message #599815]

Tue, 29 October 2013 04:27

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

But still it is not matching in this case

Shop No. 1,Vishnu Priya Building,pune--coming 
Shop No. 1,Vishnu Priya Building,|pune --required

Report message to a moderator

Re: Concatenating address fields [message #599818 is a reply to message #599817]

Tue, 29 October 2013 04:33

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

sss111ind wrote on Tue, 29 October 2013 14:57

But still it is not matching in this case

This should work perfrctly for you :

SQL> WITH address AS (SELECT 'Silver Arc Plaza,' addr1,'4th Floor, 20/1, New Palasia' addr2,'Indore' addr3 FROM dual UNION ALL
  2                    SELECT 'Shop No. 1,Vishnu Priya Building,' addr1,'' addr2,'pune' addr3 FROM dual UNION ALL
  3                    SELECT 'D/7, Siddhivinayak Nagari,' addr1,'Nr. Majura Gate, Ghod   dod Road,' addr2,'' addr3 FROM dual UNION ALL
  4                    SELECT '' addr1,'B 4, Gold Coin Complex,' addr2,'Ahmedabad' addr3 FROM dual UNION ALL
  5                    SELECT '' addr1,'' addr2,'' addr3 FROM dual)
  6  SELECT RTRIM((NVL2(ADDR1, ADDR1 || '|', ADDR1) ||
  7               NVL2(ADDR2, ADDR2 || '|', ADDR2) || ADDR3),
  8               '|') ADDRESS
  9    FROM ADDRESS;
 
ADDRESS
-----------------------------------------------------------------------------
Silver Arc Plaza,|4th Floor, 20/1, New Palasia|Indore
Shop No. 1,Vishnu Priya Building,|pune
D/7, Siddhivinayak Nagari,|Nr. Majura Gate, Ghod   dod Road,
B 4, Gold Coin Complex,|Ahmedabad

Regards,
Lalit

Report message to a moderator

Re: Concatenating address fields [message #599819 is a reply to message #599818]

Tue, 29 October 2013 04:45

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Or even (getting it right this time):

SQL> WITH address AS (SELECT 'Silver Arc Plaza,' addr1,'4th Floor, 20/1, New Palasia' addr2,'Indore' addr3 FROM dual UNION ALL
  2                    SELECT 'Shop No. 1,Vishnu Priya Building,' addr1,'' addr2,'pune' addr3 FROM dual UNION ALL
  3                    SELECT 'D/7, Siddhivinayak Nagari,' addr1,'Nr. Majura Gate, Ghod   dod Road,' addr2,'' addr3 FROM dual UNION ALL
  4                    SELECT '' addr1,'B 4, Gold Coin Complex,' addr2,'Ahmedabad' addr3 FROM dual UNION ALL
  5                    SELECT '' addr1,'' addr2,'' addr3 FROM dual)
  6  select replace(rtrim(ltrim(addr1||'|'||addr2||'|'||addr3, '|'), '|'), '||', '|') address from address;
 
ADDRESS
-----------------------------------------------------------------------------
Silver Arc Plaza,|4th Floor, 20/1, New Palasia|Indore
Shop No. 1,Vishnu Priya Building,|pune
D/7, Siddhivinayak Nagari,|Nr. Majura Gate, Ghod   dod Road,
B 4, Gold Coin Complex,|Ahmedabad
 
SQL>

Of course you could have tried working that out for yourself, replace isn't exactly complicated.

Report message to a moderator

Re: Concatenating address fields [message #599821 is a reply to message #599819]

Tue, 29 October 2013 04:49

sss111ind
Messages: 634
Registered: April 2012
Location: India

Senior Member

Sorry CookieMonster I would have tried that really.
This is really nice forum and most of the time somebody has some solution for every problem .
So finally both the codes are working perfectly .

Thank You all for your brilliant effort.

[Updated on: Tue, 29 October 2013 04:56]

Report message to a moderator

Previous Topic:	Separate string based on Comma (merged 2)
Next Topic:	getting total and single name in one query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 02:51:04 CDT 2024