MYSQL - how to string comparisons and query?

Question

+--------------------+---------------+------+-----+---------+-------+
| 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.

Jon Egerton · Accepted Answer · 2011-02-23 11:42:56Z

1

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).

edited Feb 23, 2011 at 11:42

answered Feb 23, 2011 at 10:49

Jon Egerton

41.7k11 gold badges100 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

acai Over a year ago

I'm newbie. Could you please give me the details?

acai Over a year ago

thanks. I will try your workaround.BTW, is there more brief and clean method for this issue?

Lightness Races in Orbit Over a year ago

@acai: The reason it's not clean and brief is that your scenario is quite horrid! Needing this in the first place is a design flaw in your database.

Jon Egerton Over a year ago

@acai: Ideally your DB would have two columns, one for "BOOK" and one for the value. This would simplify things considerably.

delphist · Accepted Answer · 2011-02-23 10:47:56Z

1

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

answered Feb 23, 2011 at 10:47

delphist

4,5591 gold badge24 silver badges22 bronze badges

1 Comment

acai Over a year ago

there will be problems if GKEY not sorted from small to large.

Collectives™ on Stack Overflow

MYSQL - how to string comparisons and query?

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related