0

Looking at this question on how to compare String's in SQL: https://stackoverflow.com/a/515051/409976, let's say I have the following data, where START and END are String's.

ID START  END
1   1      5
2   A      C

I performed a query to search "between" these START and END values unsuccessfully:

select * from TABLE where start <= '2' and '2' <= end -- 0 results

or

select * from TABLE where start <= 'B' and end >= 'B' -- 0 results

Since I'm searching on alphanumeric, i.e. I can't just convert to an Int/Number, how can I perform the above two queries?

Community
  • 1
  • 1
Kevin Meredith
  • 41,036
  • 63
  • 209
  • 384
  • What DB engine do you use? – juergen d Jul 23 '14 at 01:09
  • Do the values actually contain leading/trailing quotes as you show in your sample? If so, then you'll have to include them as part of the values that you're testing against. If not, then we need to know column definitions as well as DBMS version/release and possibly platform and even what client is used. – user2338816 Jul 23 '14 at 01:13

1 Answers1

0

you could use ASCII() to get the code value. It would work as long as your characters are within expected ranges etc.

SELECT * FROM table WHERE ASCII(start) <= ASCII('2') and ASCII('2') <= ASCII(end)

SELECT * FROM table WHERE ASCII(UPPER(start)) <= ASCII('B') AND ASCII('B') <= ASCII(UPPER(end))
serakfalcon
  • 3,501
  • 1
  • 22
  • 33