0

I have a column with a series of letters and numbers between aa01 and ZZ99. I can't seem to figure out how to search the column for a certain range of those such as aa00 to BZ99.

I tried using a between function such as the one below but it didnt work:

SELECT test_name
FROM Test
WHERE test_series BETWEEN 'aa00' and 'BZ99'
GROUP BY test_name

Edit:I tried adding quotes and it still does not return anything.(I added it above as well)

Edit2: MySQL is the engine.

Juststarted
  • 15
  • 3
  • 1
    Add quotes to your strings. – juergen d Jun 12 '17 at 14:31
  • `WHERE test_series BETWEEN 'aa00' and 'BZ99'`? What engine? mySQL, Postgresql? SQL server, Oracle etc? Ascii lower case comes after upper case so it would have to be 'BZ99' and 'aa00' the lowest item has to be first in the between. – xQbert Jun 12 '17 at 14:31
  • I tried adding quotes and it still does not return anything. – Juststarted Jun 12 '17 at 14:31
  • MySQL is the engine – Juststarted Jun 12 '17 at 14:34
  • in ASCII upper case characters come before lower case in terms of "code" and between requires the lower value to be first in the pair. so 'BZ99' comes before 'aa00' if case sensitivity is enabled. so reverse the pair and you should get some results. – xQbert Jun 12 '17 at 14:35
  • Thank you for your help. The statement was backwards. – Juststarted Jun 12 '17 at 14:36

2 Answers2

0

BETWEEN requires the lower value to be first. Since upper case characters come before lower case, switch the order; but be certain that's the between values you're after!.

SELECT test_name
FROM Test
WHERE test_series BETWEEN 'BZ99' and 'aa00'
GROUP BY test_name
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • 1
    `Since upper case characters come before lower case` this statement is dependent on the Collation of the column. http://sqlfiddle.com/#!9/78788a/1 – Conrad Frix Jun 12 '17 at 15:22
  • excellent comment. I did assume a collation in which upper case preceded lowercase. – xQbert Jun 12 '17 at 15:26
-1

Since I can't comment, I'll add this here as an answer instead. This explains it pretty well.

SQL BETWEEN for text vs numeric values

Edit: specifically, this part, watch your ASCII values:

Similarly, the expression:

where food_name BETWEEN 'G' AND 'O'

will match 'O', but not any other string beginning with 'O'.

Once simple kludge is to use "~". This has the largest 7-bit ASCII value, so for English-language applications, it usually works well:

where food_name between 'G' and 'O~'
Sarah M
  • 36
  • 4