Back To Programming / Scripting / Coding Forum

MySql select between 2 character columns

  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post August 2nd, 2011, 12:35 am

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.
Let's leave all our *plum* where it is and go live in the jungle ...
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post August 2nd, 2011, 12:35 am

  • Bogey
  • Bogey
  • Genius
  • User avatar
  • Joined: Jul 14, 2005
  • Posts: 8211
  • Loc: USA
  • Status: Offline

Post August 2nd, 2011, 1:35 am

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...
"Bring forth therefore fruits meet for repentance:" Matthew 3:8
  • righteous_trespasser
  • Scuffle
  • Genius
  • User avatar
  • Joined: Mar 12, 2007
  • Posts: 6228
  • Loc: South-Africa
  • Status: Offline

Post August 2nd, 2011, 1:38 am

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`,']'));
Let's leave all our *plum* where it is and go live in the jungle ...

Page 1 of 1

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: ScottG and 188 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
 
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.