Link to home
Create AccountLog in
Avatar of Sh M
Sh MFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

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
awking00 is correct, and I edited my first comment to reflect this catch.  Thank you.
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.
shmz, do you still need help with this question?
Avatar of Sh M

ASKER

Thank you