Sql/oracle query to handle comma and double quotes

Hi
I like to extract records from oracle db, by writing a awl query .
The query should check if a column has comma then put content of that column in a double quote else do not put it in double quote. This will be extracted to a comma delemited file, so all columns are separated by comma:

E.g:
A, gggg , "John , Silverado" , 12345

Thanks in advanced
shmzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
>I like to extract records from oracle db, by writing a awl query .
So are you seeking an Oracle query or a SQL Server query?

>check if a column has comma then put content of that column in a double quote else do not put it in double quote.
The SQL Server answer would go something like this, use CHARINDEX to detect if the column value has any commas in it, defined as the return value is anything other than zero, and if so surround it with double quotes.
SELECT CASE 
   WHEN CHARINDEX(',', your_column) > 0 THEN '"' + your_column + '"' 
   ELSE your_column END as name_goes_here
FROM your_table

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
Oracle is similar if you need it:
change CHARINDEX to INSTR and "+" to "||":

SELECT CASE 
   WHEN INSTR(your_value, ',') > 1 THEN '"' || your_value || '"' 
   ELSE your_value END as name_goes_here
FROM your_table

Open in new window

awking00Information Technology SpecialistCommented:
There is a slight difference. In sql server, the search is the first parameter and the field is the second.
Oracle -
where instr(field,',') > 0
SQL Server
where charindex(',', field) > 0
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Jim HornSQL Server Data DudeCommented:
awking00 is correct, and I edited my first comment to reflect this catch.  Thank you.
Jim HornSQL Server Data DudeCommented:
Another option for you is if you're using this in an ETL application such as SSIS, there is the ability within SSIS to use string delimiters such as double-quote marks to make sure a comma in those columns doesn't result in an error when the downstream process consumes the file.
Vitor MontalvãoMSSQL Senior EngineerCommented:
shmz, do you still need help with this question?
shmzAuthor Commented:
Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.