Dear All,

Just updating V3 to V4 ... nothing like being behind the times!

Hit a simple (I hope) SQL problem

I have coded all my SQL in the following form:

--

SELECT Field1 + " " + Field2 as SomeName,
Field3, Field4

FROM ATable

WHERE Field3 <> ""

--

i.e. ALL my SQL uses double quotes (") rather than single quotes (') throughout.

This is a bummer as DBISAM v4 expects:

--

SELECT Field1 + ' ' + Field2 as SomeName,
Field3, Field4

FROM ATable

WHERE Field3 <> ''

--

All my SQL is stored in a DBISAM Table (Now V4, but I have a V3 version)

I would like to write a SQL Statement to REPLACE the " with ' however, I can't see how to do this.

Statements such as

UPDATE ResourceStr
SET SQLStr = REPLACE (' " ' WITH ' ' ' IN SQLStr)

result in obvious errors ... the parser can't separate the double quotes I want to change from double quotes!!!

Also, trying to use the ESCAPE character \ (backslash) doesn't seem to work ... my query prepares & runs mut without changing the data in the table

i.e.

UPDATE ResourceStr
SET SQLStr = REPLACE ('\"" ' WITH '\'' ' IN SQLStr)

appears to run, but doesn't seem to change the data in the table.

--

Sorry if all this is a bit unclear as distinguishing between " and ' is hard on screen!

My basic problem is about 900 records in several data-tables all of quick contain double-quotes which I would like to change to single quotes.

Of course I can copy the data out to Notepad & do a searh & replace for evcery record ((( but I would rather write a few lines of SQL!

Adam

adam


try writing a few lines of pascal - much easier

Roy Lambert

Adam,
<< Statements such as

UPDATE ResourceStr
SET SQLStr = REPLACE (' " ' WITH ' ' ' IN SQLStr)

result in obvious errors ... the parser can't separate the double quotes I
want to change from double quotes!!! >>

Use this:

UPDATE ResourceStr
SET SQLStr = REPLACE (' " ' WITH ' '' ' IN SQLStr)

You have to double-up the single-quotes in order to escape them, just like
in Pascal.

--
Tim Young
Elevate Software
www.elevatesoft.com