I would like to perform a query of a personnel database with an ORDER
BY clause that ignores punctuation. For example, O'shea would sort
after Osbourne, not to the beginning of the Os.

Is this doable in the query?

-----===== Bill =====-----
--

You can tell a lot about a man by the way he handles these three
things: a rainy day, lost luggage, and tangled Christmas tree lights.

Hi,

Bill Guion wrote:
> I would like to perform a query of a personnel database with an ORDER BY
> clause that ignores punctuation. For example, O'shea would sort after
> Osbourne, not to the beginning of the Os.
>
> Is this doable in the query?

If you only have a limited number of punctuation characters to remove, you could
do something like

ORDER BY REPLACE(last_name, "'", "")

You can nest REPLACE() as many times as needed. This is admittedly ugly and
will defeat indexes, but it's the only thing I can think of.

If the sorting must be efficient, you might consider maintaining another column
on the table, which has the name without punctuation. You could then index this
column and ORDER BY it without using any string manipulation.

Baron