MySQL BETWEEN查询从数据库中的文本字段获取数据

Question

I have a query :

$sql = "SELECT * FROM table WHERE  CONCAT(Prod, Serial) BETWEEN '$start' AND '$end'";

Where Prod and Serial are stored in MySQLi database as a string. I need to update details of items between say AA1000 and AA1005. When I run this query it updates details of units between AA1000 and AA1005 and, "as a bonus", all between AA10000 and AA10050. Is there a way to get exact match for the text type fields using BETWEEN query?

Thanks!

P.S. I know that I need update query to update details. I'm using SELECT for testing purposes.

Answer 1

This query should do it:

SELECT *
FROM TABLE
WHERE Prod = LEFT('$start', 2)
AND CAST(Serial AS DECIMAL) BETWEEN SUBSTRING('$start', 3) AND SUBSTRING('$end', 3);

You could also split the start and end strings apart in PHP:

$prod = substr($start, 0, 2);
$start_code = substr($start, 2);
$end_code = substr($end, 2);

$sql = "SELECT *
FROM TABLE
WHERE Prod = '$prod'
AND CAST(Serial AS DECIMAL) BETWEEN $start_code AND $end_code";