Re: apostrophes in an SELECT SQL statement

Jason Byors <jbyors_at_ssi.sony.com> wrote:

>How could the following SELECT SQL statement be written so that it
>will work:
 

>SELECT * FROM movies WHERE title = 'Muriel's Wedding'
 

>The apostrophe in the word Muriel's is not allowing this statement to
>be processed. Is there any way to make such a statement work, or
>would I have to remove all the apostrophes from my table?
 

>Thank you for your help,
>jb

You can use: WHERE title = 'Muriel' || '''' || 's Wedding' (I can't remember the number of apostrophies).

A completely different solution may be to create an extra column on the table, say stitle (as in stripped-title) and whenever the title is inserted into this table, insert the 'REPLACE(title, '''',null) into this column. (Make sure you have a unique index on this column). When you look for a title, use WHERE stitle = 'Muriels Wedding'! This is better than using WHERE REPLACE(title,'''',null) = 'Muriels Wedding' since if this column is indexed, the index would not be used.

Otherwise, I don't know!

Good luck. Received on Fri Jul 19 1996 - 00:00:00 CEST