CODEWITHSUNDEEP
mysql
acai
acai

Reputation: 25

MYSQL - how to string comparisons and query?

+--------------------+---------------+------+-----+---------+-------+
| ID                 | GKEY          |GOODS | PRI | COUNTRY | Extra |
+--------------------+---------------+------+-----+---------+-------+
| 1                  | BOOK-1        | 1    | 10  |         |       |
| 2                  | PHONE-1       | 2    | 12  |         |       |
| 3                  | BOOK-2        | 1    | 13  |         |       |
| 4                  | BOOK-3        | 1    | 10  |         |       |
| 5                  | PHONE-2       | 2    | 10  |         |       |
| 6                  | PHONE-3       | 2    | 20  |         |       |
| 7                  | BOOK-10       | 2    | 20  |         |       |
| 8                  | BOOK-11       | 2    | 20  |         |       |
| 9                  | BOOK-20       | 2    | 20  |         |       |
| 10                 | BOOK-21       | 2    | 20  |         |       |
| 11                 | PHONE-30      | 2    | 20  |         |       |
+--------------------+---------------+------+-----+---------+-------+

Above is my table. I want to get all records which GKEY > BOOK-2, Who can tell me the expression with mysql?

Using " WHERE GKEY>'BOOK-2' " Cannot get the correct results.

Upvotes: 0

Views: 85

Answers (2)

Jon Egerton
Jon Egerton

Reputation: 41579

How about (something like):

(this is MSSQL - I guess it will be similar in MySQL)

select 
   *
from
   (
      select 
         *,
         index = convert(int,replace(GKEY,'BOOK-',''))
      from table
      where 
         GKEY like 'BOOK%'
   ) sub
where
   sub.index > 2

By way of explanation: The inner query basically recreates your table, but only for BOOK rows, and with an extra column containing the index in the right data type to make a greater than comparison work numerically.

Alternatively something like this:

select
   *
from table
where
   (
     case
        when GKEY like 'BOOK%' then
           case when convert(int,replace(GKEY,'BOOK-','')) > 2 then 1
           else 0
           end
        else 0
        end
   ) = 1

Essentially the problem is that you need to check for BOOK before you turn the index into a numberic, as the other values of GKEY would create an error (without doing some clunky string handling).

Upvotes: 1

delphist
delphist

Reputation: 4549

SELECT * FROM `table` AS `t1` WHERE `t1`.`id` > (SELECT `id` FROM `table` AS `t2` WHERE `t2`.`GKEY`='BOOK-2' LIMIT 1)

Upvotes: 1

Related Questions