Thread: ORDER BY and ignoring the , a , and an

Is it possible to have the ORDER BY option ignore the articles "the ", "a ",
and "an " at the beginning of a VARCHAR, CHAR, TEXT, et cetera field? In
other words, I want my results to come back alphabetized, while ignoring the
aforementioned articles.

Brett Stoppel
Assistant Web Administrator
University of Kansas Academic Computing Services
bstoppel@ku.edu - 785.864.0462 - www.ku.edu

"Stoppel, Brett W" <bstoppel@ku.edu> writes:

> Is it possible to have the ORDER BY option ignore the articles "the ", "a ",
> and "an " at the beginning of a VARCHAR, CHAR, TEXT, et cetera field? In
> other words, I want my results to come back alphabetized, while ignoring the
> aforementioned articles.

Only way I can think of would be to write a function to do the
stripping yourself, say 'strip_articles(text) RETURNS text' and then
in your SELECTS, do 'ORDER BY strip_articles(mycolumn)'.  For
performance you might also want to create an index on that same
expression.

You could write the function in any of the available server-side
languages (pgsql, perl, tcl).

-Doug

> Is it possible to have the ORDER BY option ignore the articles "the ", "a
",
> and "an " at the beginning of a VARCHAR, CHAR, TEXT, et cetera field? In
> other words, I want my results to come back alphabetized, while ignoring
the
> aforementioned articles.

I suppose you could write a function (say strip_articles) to strip those
words out (and anything else you'd like), then do an ORDER BY
strip_articles(MyField).

Greg

Hi there!

I am using pgsql 7.0/ecpg C interface on Redhat 6.1.
Is it possible to store and retrieve fixed length binary data (<100bytes
in length) in text or varchar (or any other data type)? Using ecpg, I want
to "memcpy" from my local variable to a field in the table and back.
Postgresql seems to do a "strcpy" instead. If someone can help me, I
would greatly appreciate it! I checked the archives and examples but found
no relevant articles.

THANKS!!!
Swami.