SQL Select between mixture of text and numeric

Not sure if it’s possible to do this in SQL but… I’m having difficulty selecting results between certain criteria. I have a column that is a mixture of text and numeric. For example: LOC:05-04-01. I’m wanting to select items between two locations. EG: between LOC:05-04-01 and LOC:05-04-20.

I’ve tried using the standard BETWEEN statement but it returns an empty result.

$loc1 = 'LOC:05-04-01';
$loc2 = 'LOC:05-04-20';
$sql = $dbh->prepare("SELECT * FROM table WHERE location BETWEEN ? AND ? ORDER BY location DESC");
$sql->execute([$loc1,$loc2]);
while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
echo $row['ID'].': '.$row['location'].'<br>';
}

My database is similar to below:

ID | Location
1 | LOC:05-04-01
2 | LOC:05-04-02
3 | LOC:05-04-05
4 | LOC:06-04-01
5 | LOC:06-04-02
6 | LOC:06-04-10

I’m expecting to see a list of locations out of the above query such as:

1: LOC:05-04-01
2: LOC:05-04-02
3: LOC:05-04-05

Answer

This code should do what you want:

SELECT *
FROM <table>
WHERE location BETWEEN 'LOC:05-04-01' AND 'LOC:05-04-20'
ORDER BY location DESC;

You are doing string comparisons, and the values compare as strings.

You should test this using a direct query on the database. If this doesn’t work, then you might have data in columns that you don’t expect — say the hyphens are really a different character.

If the PHP code does not work, something is going wrong at that level. You might have an error in your query (say connected to the wrong database). You might have bad characters in your constants.