Transact-SQL
Feb. 14th, 2005 06:47 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
sylvarSo 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:
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...
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...
