Adam Adam - 3 months ago 13
SQL Question

SQL BETWEEN case sensitivity

I'd like to create a BETWEEN parameter that returns values that are alphabetic (i.e. between A-Z and a-z inclusive). Is there a way to do this without using two BETWEEN clauses?

Answer

You don't say what platform you are using

WHERE UPPER(fieldname) BETWEEN 'A' and 'Z'

or

WHERE UCASE(fieldname) BETWEEN 'A' and 'Z'

you might want to get rid of some pesky spaces

WHERE UPPER(TRIM(fieldname)) BETWEEN 'A' and 'Z'

This will be slow, we can't use fieldname in an index if we run a function on it. So we are forcing a tablescan, which means two betweens and an or will be faster if fieldname is indexed and the table has some number of rows.