Home » SQL & PL/SQL » SQL & PL/SQL » SPOOL question: Adding quotes around field only if a comma exists within? (Oracle 10i)
SPOOL question: Adding quotes around field only if a comma exists within? [message #448897] Thu, 25 March 2010 11:07 Go to next message
BJerols
Messages: 14
Registered: March 2010
Junior Member
Hi all,

I searched the forum but could not find an answer. I am creating an extract via SPOOL that will send the output to a CSV file. I am comfortable concatenating commas inbetween the fields, the problem lies in text fields such as last_name which may contain a comma. The requirements of my client state to put double quotes around text fields ONLY if they contain a comma (so as not to throw off the CSV file obviously).

The only thing I could think of was a nested DECODE checked every character of every text field for a , and if it finds one to put " and if not to put nothing. This would get very tedious to program it as there are many text fields and they can be very long. I may even run out of nested DECODE statements (I forget what the limit is now).

Any help would be appreciated!


As a backup, I can approach the client about double quoting every text field, but would like to get the experts thoughts first. Thanks!


Brent
Re: SPOOL question: Adding quotes around field only if a comma exists within? [message #448900 is a reply to message #448897] Thu, 25 March 2010 11:13 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> WITH DATA AS (SELECT '1,2' a FROM dual 
  2                UNION
  3                SELECT '13' a FROM dual )
  4  SELECT (CASE WHEN INSTR(a, ',') = 0 THEN a
  5              ELSE '"'||a||'"'
  6              END) new_a
  7  FROM DATA;

NEW_A
-----
"1,2"
13

SQL> 


I wouldn't generally use Decode for anything, case is much nicer.
Re: SPOOL question: Adding quotes around field only if a comma exists within? [message #448901 is a reply to message #448897] Thu, 25 March 2010 11:14 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Though I really have to wonder why you don't just double quote everything, got to be easier surely?
Re: SPOOL question: Adding quotes around field only if a comma exists within? [message #448902 is a reply to message #448901] Thu, 25 March 2010 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
That was also my first thought, why bother with or without comma, enclose everything.

Regards
Michel
Re: SPOOL question: Adding quotes around field only if a comma exists within? [message #448903 is a reply to message #448900] Thu, 25 March 2010 11:18 Go to previous messageGo to next message
BJerols
Messages: 14
Registered: March 2010
Junior Member
thanks for the super quick reply!

I agree " with all text fields would be easier, but I don't like to say No to clients if I don't have to and don't mind a challenge.

I will give the CASE command a go, thanks again! I wonder if I can use LIKE '%,%' with CASE?
Re: SPOOL question: Adding quotes around field only if a comma exists within? [message #448904 is a reply to message #448897] Thu, 25 March 2010 11:24 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
Like would work as well.

I would suggest asking the client what they think the point of selectively double-quoting is. It just adds unecessary complexity and excell doesn't display them anyway. I can see absolutely no upside to this and several downsides - performance, maintenance.
Re: SPOOL question: Adding quotes around field only if a comma exists within? [message #448906 is a reply to message #448897] Thu, 25 March 2010 11:26 Go to previous messageGo to next message
BJerols
Messages: 14
Registered: March 2010
Junior Member
Thanks for all your help guys! LIKE worked if you omit the field name from expression like so


SELECT
CASE
WHEN last_name LIKE '%,%' THEN '"'
ELSE ''
END,
last_name,
CASE
WHEN last_name LIKE '%,%' THEN '"'
ELSE ''
END
FROM participant;
Re: SPOOL question: Adding quotes around field only if a comma exists within? [message #448907 is a reply to message #448904] Thu, 25 March 2010 11:28 Go to previous messageGo to next message
BJerols
Messages: 14
Registered: March 2010
Junior Member
cookiemonster wrote on Thu, 25 March 2010 11:24
Like would work as well.

I would suggest asking the client what they think the point of selectively double-quoting is. It just adds unecessary complexity and excell doesn't display them anyway. I can see absolutely no upside to this and several downsides - performance, maintenance.



I agree with you although they are not opening in excel, they are importing the file into a system (actually they are providing this output to another vendor for use with them and it is that vendor's requirements).

I will ask them, but I'm glad to learn CASE and this functionality now.


Thanks again, you have all been very helpful and super fast. Have a great day!
Re: SPOOL question: Adding quotes around field only if a comma exists within? [message #448908 is a reply to message #448897] Thu, 25 March 2010 11:32 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
I have to wonder what they are doing with it. After all whatever they are importing it into is going to have to handle double-quotes and if it handles them properly then it shouldn't matter how many there are. And it's easier to handle them if you assume they are always there.

But if it's a third party you probably aren't going to have much control over the requirements.
Re: SPOOL question: Adding quotes around field only if a comma exists within? [message #448984 is a reply to message #448903] Fri, 26 March 2010 02:43 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
BJerols wrote on Thu, 25 March 2010 17:18
I agree " with all text fields would be easier, but I don't like to say No to clients if I don't have to and don't mind a challenge.


I totally agree with what cookiemonster said, and although your approach may seem goof, as a customer I would prefer a sound advice on how things can be simplified over a supplier that blindly follows my requirements!
(I realize that this can come across as a harsh judgement on what you do, but that is not how I mean it. Haven't had my coffee yet, so not able to find the correct tone in English Smile )
Previous Topic: Before and After trigger issue
Next Topic: sys.anydata.ConvertClob
Goto Forum:
  


Current Time: Wed Dec 07 18:25:36 CST 2016

Total time taken to generate the page: 0.08984 seconds