Sort ignores apostrophe

[Ed Adamthwaite]

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.

 

[Allen Browne]

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.

 

[Ed Adamthwaite]

Thanks Allen,
It works beautifully.
Another of those lateral thinking tricks which triggers a new approach to
some problems.
I can see this being used elsewhere...
Thanks again and have a happy new year,
Ed.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Michel Walsh]

Hi,


Win32 does so for ' and -. It is a built-in feature for the OS, the reason
supplied being that these two characters are often to be transparent, for
search, in English, or, if you prefer, not being so, would confuse casual
users. Note that Win32 is quite old in design, and today, a different
implementation would probably be done, but we have to leave with that,
somehow, isn't it?



Hoping it may help,
Vanderghast, Access MVP

 

[Tom Ellison]

Dear Allen:

Nicely put.

If the database is really large, this would be a reason to move to MSDE.
First, if it grows even larger and passes the 2GB limit, you can migrate
readily.

With MSDE, you can create a calculated column and index on that. With your
solution (the best I know of for Jet) you must maintain this column with
code anywhere it is possible to change the Surname. Yuck! MSDE/SQL Server
will do that for you automatically, and maintain the index for you. SWEET!

Tom Ellison

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.