Sql/oracle query to handle comma and double quotes

shmz
shmz used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
>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

Most Valuable Expert 2012
Distinguished Expert 2018

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 Specialist

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
awking00 is correct, and I edited my first comment to reflect this catch.  Thank you.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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ãoIT Engineer
Distinguished Expert 2017

Commented:
shmz, do you still need help with this question?

Author

Commented:
Thank you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial