Right single quotation mark errors

[Robert Garrett]

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''investisseur s'

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

[John Bell]

Re: Right single quotation mark errors

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.
[color=blue]
> select * from Export where libelle_court=' Recherche d''investisseur s'[/color]

What does

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

return?

Also check out:

http://msdn.microsoft.com/library/de...on_03_7mch.asp

http://msdn.microsoft.com/library/de...archvalues.asp

Collation will not effect this.

John

"Robert Garrett" <rgagarrett@hot mail.com> wrote in message
news:b9c50dd6.0 311030229.1ea4c 288@posting.goo gle.com...[color=blue]
> 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''investisseur s'
>
> 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[/color]

[John Bell]

Re: Right single quotation mark errors

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.btint ernet.com...[color=blue]
> Hi
>
> In general if there is a quotation mark in the field it can be escaped[/color]
with[color=blue]
> another quotation mark, therefore I am not sure why the second example[/color]
does[color=blue]
> not work.
>[color=green]
> > select * from Export where libelle_court=' Recherche d''investisseur s'[/color]
>
> What does
>
> select * from Export where libelle_court like 'Recherche d%'
>
> return?
>
> Also check out:
>
>[/color]
http://msdn.microsoft.com/library/de...on_03_7mch.asp[color=blue]
>
>[/color]
http://msdn.microsoft.com/library/de...archvalues.asp[color=blue]
>
> Collation will not effect this.
>
> John
>
> "Robert Garrett" <rgagarrett@hot mail.com> wrote in message
> news:b9c50dd6.0 311030229.1ea4c 288@posting.goo gle.com...[color=green]
> > 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''investisseur s'
> >
> > 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[/color]
>
>[/color]

[Robert Garrett]

Re: Right single quotation mark errors

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.

Rob