double quotation mark makes an error

**dotJoon** · Nov 18, 2004 01:35

(1) 'Homerun!' he yelled.
(2) "Homerun!" he yelled.

I have the data above in myTable.
And I have key "homerun!"

Let's see the following code and its result.

Code:

code1 
where say like "%#key#' %" 

result1 
(1) 'Homerun!' he yelled. 

code2 
where say like "%#key#" %" 

result2 
(2) "Homerun!" he yelled.

I like to produce record(1) and record(2) at a same time.

So I made the following code3, and it work fine when the key is "homerun!" or other words.

Code:

code3
where (say like "%#key#' %") 
        or 
      (say like '%#key#" %')
result3
(1) 'Homerun!' he yelled. 
(2) "Homerun!" he yelled.

But above code seems make an SQL error when the key word is double quotation mark(").

Do you see why it makes an errors when the key is double quotation mark(")?

(Strangely, the key word the single quotation mark(') doesn't make an error. )

Thanks in Advance

**dotJoon** · Nov 18, 2004 19:53

As any of you don't reply, I think my question was something wrong.

Okay,
I'll make the point clearer
Please forget about my question above.
The following is the main point of my question.

...
(7) 'Homerun!' he yelled.
(8) He is running fast.
...

I have the data above in myTable.

I like to retrieve the record(7) but not to retrieve the record (8) with a key word "run".

So I made the following code, and it works fine for that purpose.

Code:

  select ID, say 
  from myTable
  where (say like "%#form.keyword#!' %")

But problem with the above code is that it causes a coldfusion error, i.e."Syntax error or access violation:" when the keyword is double quotation mark(").

Do you see what is the problem?

How can I retrieve record (7) only
with the keyword "run"
without any Syntax error when the keyword is double quotation mark(")?

Thanks in Advance