Re: Sort ignores apostrophe
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Sat, 31 Dec 2005 11:08:57 +0800
Why indeed, Ed. (You should see what they did to hypens, where are now
sorted/matched inconsistently, depending on whether the field is indexed.)
Could you use the Replace() function to replace the single quote with a
space or other symbol, e.g.:
ORDER BY Replace([Surname], "'", "@")
It will kill the indexing, so hopefully we are not talking about too many
thousands of records here. If it is a real phone book, you might need to add
another field containing the actually sorting string, and index it.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Ed Adamthwaite" <adamedSpamMe@xxxxxxxxxxxxxxxx> wrote in message
news:OJN76taDGHA.916@xxxxxxxxxxxxxxxxxxxxxxx
> Hi all,
> When I do a sort on a surname field, it appears as though the apostrophe
> is specifically excluded from a sort in SQL. For example, O Dea comes
> before Oates but O'Dea comes after. If I do a typo and use a double-quote,
> O"Dea comes before Oates.
> space = ASCII 32
> double quote = ASCII 34
> single quote = ASCII 39
> O = ASCII 79
> I look to the sky and plaintively cry "Why? o Microsoft, why?"
> Is there a way to do a sort that mimics the ordering in a phone book?
> (We'll ignore "Macs" for the moment).
> Regards,
> Ed.
.
- References:
- Sort ignores apostrophe
- From: Ed Adamthwaite
- Sort ignores apostrophe
- Prev by Date: Re: Crosstab Value field formula too long, min() and max() function help sought
- Next by Date: Problem with Dual subselect
- Previous by thread: Sort ignores apostrophe
- Next by thread: Find and Replace against set of rules in 2nd table
- Index(es):
Relevant Pages
|
Loading
