Home » SQL & PL/SQL » SQL & PL/SQL » Concat function
Concat function [message #317458] Thu, 01 May 2008 08:43 Go to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

(a.last_name||'#'||b.first_name|| '#'||c.middle_name)


I am concatenating three columns as above and seprating them with '#'. There are some values which are null in those columns, for that i want to skip '#'. so, if there is null value, i want to keep that as blank.


Thanks and Appreciate your time in advance
Re: Concat function [message #317461 is a reply to message #317458] Thu, 01 May 2008 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You want to replace null by a blank? Use NVL.
You want to ignore null field, use DECODE/CASE.

Regards
Michel
Re: Concat function [message #317470 is a reply to message #317461] Thu, 01 May 2008 09:05 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Thank Michel for you time.. I am trying to explain my question again. Or i will really appreciate if you can help with some more details.

Use NVL:

nvl((a.last_name||'#'||b.first_name|| '#'||c.middle_name)
,' ')


Even though all three columns are null in some cases.. It's going to add '#' anyway. So, for the null values the data will look like this
##


So,if it finds any null value on those columns, i don't want to add '##'.

Appreciate you time once again
Re: Concat function [message #317476 is a reply to message #317470] Thu, 01 May 2008 09:13 Go to previous messageGo to next message
shoaib123
Messages: 118
Registered: December 2007
Location: Chicago
Senior Member

Never mind .. don't want to disturb you soo uch with this little question .. i found the soloution.
Re: Concat function [message #317483 is a reply to message #317476] Thu, 01 May 2008 09:33 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't disturb me.
Please post the solution for others.

Regards
Michel

Previous Topic: Master Detail with Connect by
Next Topic: SP2-0310 error
Goto Forum:
  


Current Time: Fri Dec 09 19:16:55 CST 2016

Total time taken to generate the page: 0.15591 seconds