Transact-SQL

[sylvar]

So I'm learning Transact-SQL for Sybase, particularly as it applies to our library automation system, and someone asks "How can I get a list of all books with all-capital titles?"

Offhand I thought this would work:

select bib.bib#, title.processed
from bib, title
where bib.bib# = title.bib#
and title.processed = upper(title.processed)


But it turns out that our SQL server was installed as case-insensitive for developers' convenience, so the answer is that you can't do it this way. But you can if you do a loop through all lower-case letters of the alphabet (preferably from a table of letter frequency), breaking if you find any lower-case letters (searching by ASCII code), and printing the book's title if none are found. (This also includes books like "1, 2, 3", but that's a minor problem.

You shouldn't do this because it's time-consuming to check the entire title of every single book. It wouldn't be necessary to do this if the server were case-sensitive, but oh well.

The better answer is to export a list of titles to a text file and pipe it through a script that does what case-insensitive installations of SQL can't do.

Ugh. Perl Boy here was trying 'eq', '==', and all sorts of other operators until this finally dawned on him...

Comments

[sylvar.livejournal.com]

Well, it doesn't affect the opac that much, since the opac searches are run against indexes, not the live database.