CODEWITHSUNDEEP
mysqlsql
John Isaiah Carmona
John Isaiah Carmona

Reputation: 5366

Comparing number in formatted string in MySQL?

I have a PolicyNo column in my table in MySQL with a format like this:

XXXX-000000

A four capital-case characters followed by a dash and a six digit number.

The six digit number is incremental, adding 1 for the next row, and the the four characters is always the same for all rows. The PolicyNo column is unique with a type of varchar(11).

If ordered, it will look like this:

XXXX-000001
XXXX-000002
XXXX-000003
...

Now I want to get all PolicyNo whose number is greater than a specified number.

For example: Retrieve all PolicyNo greater than 'XXXX-000100':

XXXX-000101
XXXX-000102
XXXX-000103
...

I test this query and it works fine, but I just didn't know if it is really safe to do such:

SELECT  'XXXX-000099' > 'XXXX-000098'
    ,   'XXXX-000099' > 'XXXX-000100'
    ,   'XXXX-000099' > 'XXXX-000101'

Result:
+-------------------------------+-------------------------------+-------------------------------+
| 'XXXX-000099' > 'XXXX-000098' | 'XXXX-000099' > 'XXXX-000100' | 'XXXX-000099' > 'XXXX-000101' |
+-------------------------------+-------------------------------+-------------------------------+
|                             1 |                             0 |                             0 |
+-------------------------------+-------------------------------+-------------------------------+

Is there any other way to do this or is it already OK to use this?

Upvotes: 0

Views: 873

Answers (2)

Vardan Gupta
Vardan Gupta

Reputation: 3595

You can also use SUBSTRING function provided by MySQL, like the following query.

SELECT count(*) FROM Table1 where substring(policyNo,6)>YOUR_RANGE;

here 6 is passed as the 6 digit number start from 6th position And if you do want to pass initial 4 charecter as well then you can use following query. Here second where clause will take intial 4 letters from the policyNo.

SELECT count(*) FROM Table1 where substring(policyNo,6)>YOUR_RANGE AND substring(policyNo,1,4) = 'ABCD'

Upvotes: 1

doublesharp
doublesharp

Reputation: 27687

Because your numbers are zero padded, as long as the four letter prefix is the same and always the same length, then this should work as MySQL will do a lexicographical comparison.

Note that one less 0 in the padding will cause this to fail:

SET @policy1 = 'XXXX-00099';
SET @policy2 = 'XXXX-000598';
SELECT @policy1, @policy2, @policy1 > @policy2 AS comparison;
=========================================
> 'XXXX-00099', 'XXXX-000598', 1

If you need to truly compare the numbers at the end, you will need to parse them out and cast them:

SET @policy1 = 'XXXX-00099';
SET @policy2 = 'XXXX-000598';
SELECT @policy1, @policy2, 
   CONVERT(SUBSTRING(@policy2, INSTR(@policy2, '-')+1), UNSIGNED) >
   CONVERT(SUBSTRING(@policy2, INSTR(@policy2, '-')+1), UNSIGNED) AS comparison;
=========================================
> 'XXXX-00099', 'XXXX-000598', 0

Upvotes: 1

Related Questions