sql alphanumeric with range start & end

Question

I have a problem, how to search alphanumeric with range (start & end) such as below :

table

if i use BETWEEN, the result will problem with the number that have letter at the end, in pic/table = Input 2.

So, how can i to get the Id 6 result when keyin HSF3145858B

pls advise

Thank You

What database (mysql, postgres, sql server...) are you using? — peterm, Mar 03 '17 at 02:27
put your database schema and some sample data instead of image attachment — sumit, Mar 03 '17 at 02:30
$tsql = "SELECT Product FROM tbl_serial_range WHERE '$serialno' BETWEEN Start AND End"; - my current query — twister, Mar 03 '17 at 02:38
if im enter Input 1..the result is ok...but when im enter input 2.. the result will display Id 5 & 6... — twister, Mar 03 '17 at 02:45
Please *[edit]* your question to add more information (and then you can indicate the code by highlighting it and hitting the `{}` button) — Damien_The_Unbeliever, Mar 03 '17 at 08:44

score 0 · Answer 1 · answered Mar 03 '17 at 03:12

That is because between uses alphabetical ordering. You should probably add another check to confirm that the searched code is the same length as the start and/or end. This could be done by putting parenthesis around the first clause and adding AND (len('$serialno') = len (Start))

This would lead to the following which should work

$tsql = "SELECT Product FROM tbl_serial_range WHERE ('$serialno' BETWEEN Start AND End) AND (len('$serialno') = len (Start)) ";

sql alphanumeric with range start & end

1 Answers1