All Apps and Add-ons

Using DB connect to pull data and need to replace "" quotes in a string.

New Member

Here is my query:

SELECT REPLACE([TEXT],'"','''') as EVENT from DB_name

This version of the query works in the DB connect editor, but does not work when saved as a job.

Adding an escape character:
SELECT REPLACE([TEXT],'\"','''') as EVENT from DB_name
Allows the query to work when saved, but does not replace the quotes at all.

I am trying to use a simple query that works in SQL. What am I missing?

0 Karma

New Member

Try using the CHAR() function which can be used to set a single ANSI character in your code. E.g. CHAR(34) will print a single double-quote ". To come up with ANSI codes, you can also use ANSI('"').

For your problem, you can use:
SELECT REPLACE([TEXT], CHAR(34) ,'''') as EVENT from DB_name

This should work on most database systems...

0 Karma