Right single quotation mark errors

Hi,
I've created a table in SQL Server 2000 and I'm now trying to search
through the data and return specific rows. I'm using this command:

select * from Export where libelle_court='Recherche d'investisseurs'

The problem is this: The search fails whenever there is a curly
single quotation mark within the table field ( ' as opposed to ' ).

For example, if the field entry in my table is this:
Recherche d'investisseurs

then both of the following commands retun no fields:
select * from Export where libelle_court='Recherche d'investisseurs'
select * from Export where libelle_court='Recherche d''investisseurs'

However, if the field entry in my table is this:
Recherche d'investisseurs

then both of the commands quoted above succeed.

How can I get SQL Server to treat the curly quotation mark correctly
and return the right results? I've tried changing the collation but
with no success.

Thanks,

RobHi

In general if there is a quotation mark in the field it can be escaped with
another quotation mark, therefore I am not sure why the second example does
not work.

> select * from Export where libelle_court='Recherche d''investisseurs'

What does

select * from Export where libelle_court like 'Recherche d%'

return?

Also check out:

http://msdn.microsoft.com/library/d...con_03_7mch.asp

http://msdn.microsoft.com/library/d...earchvalues.asp

Collation will not effect this.

John

"Robert Garrett" <rgagarrett@.hotmail.com> wrote in message
news:b9c50dd6.0311030229.1ea4c288@.posting.google.c om...
> Hi,
> I've created a table in SQL Server 2000 and I'm now trying to search
> through the data and return specific rows. I'm using this command:
> select * from Export where libelle_court='Recherche d'investisseurs'
> The problem is this: The search fails whenever there is a curly
> single quotation mark within the table field ( ' as opposed to ' ).
> For example, if the field entry in my table is this:
> Recherche d'investisseurs
> then both of the following commands retun no fields:
> select * from Export where libelle_court='Recherche d'investisseurs'
> select * from Export where libelle_court='Recherche d''investisseurs'
> However, if the field entry in my table is this:
> Recherche d'investisseurs
> then both of the commands quoted above succeed.
> How can I get SQL Server to treat the curly quotation mark correctly
> and return the right results? I've tried changing the collation but
> with no success.
> Thanks,
> Rob|||Just another thought, depending on what the datatypes and options are, check
out trailing spaces. (Also see ANSI_PADDING in BOL)

John

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:bo5fp2$6q8$1@.sparta.btinternet.com...
> Hi
> In general if there is a quotation mark in the field it can be escaped
with
> another quotation mark, therefore I am not sure why the second example
does
> not work.
> > select * from Export where libelle_court='Recherche d''investisseurs'
> What does
> select * from Export where libelle_court like 'Recherche d%'
> return?
> Also check out:
>
http://msdn.microsoft.com/library/d...con_03_7mch.asp
>
http://msdn.microsoft.com/library/d...earchvalues.asp
> Collation will not effect this.
> John
> "Robert Garrett" <rgagarrett@.hotmail.com> wrote in message
> news:b9c50dd6.0311030229.1ea4c288@.posting.google.c om...
> > Hi,
> > I've created a table in SQL Server 2000 and I'm now trying to search
> > through the data and return specific rows. I'm using this command:
> > select * from Export where libelle_court='Recherche d'investisseurs'
> > The problem is this: The search fails whenever there is a curly
> > single quotation mark within the table field ( ' as opposed to ' ).
> > For example, if the field entry in my table is this:
> > Recherche d'investisseurs
> > then both of the following commands retun no fields:
> > select * from Export where libelle_court='Recherche d'investisseurs'
> > select * from Export where libelle_court='Recherche d''investisseurs'
> > However, if the field entry in my table is this:
> > Recherche d'investisseurs
> > then both of the commands quoted above succeed.
> > How can I get SQL Server to treat the curly quotation mark correctly
> > and return the right results? I've tried changing the collation but
> > with no success.
> > Thanks,
> > Rob|||Thanks for the help.

I'm not sure exactly what the problem was but it has gone now. I was
working on a number of things so I don't know quite what it was that
fixed the problem. I thought it might be because I was changing the
collation, but I cannot use this to repeat the fault. The web sites
were useful, though, so thanks again for the help.

Robsql