[Solved] select * from table where ticket_id between R1.1.2 And R106.2.3 this is not work [closed]

Question

I think I’ve figured out what you are trying to do now. For example, you are expecting the query to match R2.1.2 which lies between R1.1.2 and R106.2.3 according to your id scheme.

First of all, your SQL is syntactically invalid. Assuming that these ids are represented by a VARCHAR or similar, you need to use string literals in the SQL; e.g.

    select * from table where ticket_id between 'R1.1.2' and 'R106.2.3';

But that doesn’t solve the problem. The reason is that the above is equivalent to

    select * from table where ticket_id >= 'R1.1.2' 
                        and ticket_id <= 'R106.2.3';

where >= and <= are regular SQL string comparisons. And that is the problem. Because according to regular SQL string comparison, 'R2.1.2' is larger than 'R106.2.3'!!.

In fact, I don’t think there is an easy way to express the range check in SQL. You might be able to do it with a stored procedure that breaks the ticket id string into parts, converts the parts to numbers and compares them. That is messy and would cause the query optimizer indigestion. MySQL will end up doing table scans, no matter what indexes you define.

If you want query the ids as number triples / tuples, then you really ought to change the schema, replacing the VARCHAR field with three (or more) INT fields.

Alternatively, if you know that the fields are in specific ranges, you could represent the id component with leading zeros; e.g. R0000001.01.02. However, this breaks if your range assumptions are incorrect; e.g. when you have too many primary ticket numbers.

Accepted Answer