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

[John Bell]

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/de...on_03_7mch.asp

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

Collation will not effect this.

John

"Robert Garrett" <rg********@hotmail.com> wrote in message
news:b9**************************@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

[John Bell]

Just another thought, depending on what the datatypes and options are, check
out trailing spaces. (Also see ANSI_PADDING in BOL)

John

"John Bell" <jb************@hotmail.com> wrote in message
news:bo**********@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/de...on_03_7mch.asp
http://msdn.microsoft.com/library/de...archvalues.asp
Collation will not effect this.

John

"Robert Garrett" <rg********@hotmail.com> wrote in message
news:b9**************************@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

[Robert Garrett]

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