MySql select between 2 character columns

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

I have a table that looks similar to this example table:
MYSQL Code: [ Select ]
CREATE TABLE `example`(
`id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`title` VARCHAR(32) NOT NULL,
`min_character` CHAR(1) NOT NULL,
`max_character` CHAR(1) NOT NULL
)ENGINE=MyISAM;
  1. CREATE TABLE `example`(
  2. `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  3. `title` VARCHAR(32) NOT NULL,
  4. `min_character` CHAR(1) NOT NULL,
  5. `max_character` CHAR(1) NOT NULL
  6. )ENGINE=MyISAM;


Let's say I have the following data in the table:
MYSQL Code: [ Select ]
INSERT INTO `example` VALUES(NULL,'tester1','A','C');
INSERT INTO `example` VALUES(NULL,'tester1','D','E');
INSERT INTO `example` VALUES(NULL,'tester1','F','H');
  1. INSERT INTO `example` VALUES(NULL,'tester1','A','C');
  2. INSERT INTO `example` VALUES(NULL,'tester1','D','E');
  3. INSERT INTO `example` VALUES(NULL,'tester1','F','H');


I want to construct a query to find the title where 'character'="B", thus it must return the row where `min_character`="A" and `min_character`="C" but "WHERE `min_character` > ='B' AND `max_character` <= 'B'" does not work and I am unsure of how to get the right result.
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Bogey
  • Genius
  • Genius
  • Bogey
  • Posts: 8388
  • Loc: USA

Post 3+ Months Ago

Use REGEX?
Code: [ Select ]
SELECT * FROM example WHERE min_character REGEXP ""

Use NOT LIKE?
Code: [ Select ]
SELECT * FROM example WHERE min_character NOT LIKE "B"

!= or <> (I think)
Code: [ Select ]
SELECT * FROM example WHERE min_character != "B"
Code: [ Select ]
SELECT * FROM example WHERE min_character <> "B"

I'm not exactly sure on the last two...
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Posts: 6230
  • Loc: South-Africa

Post 3+ Months Ago

I ended up using REGEXP, the query now looks like follows:

MYSQL Code: [ Select ]
SELECT * FROM `example` WHERE ('D' REGEXP concat('^[',`min_character`,'-',`max_character`,']'));

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 106 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.