CODEWITHSUNDEEP
mysqlselectspaces
XCS
XCS

Reputation: 28137

MySQL select compare numbers from space separated string

I have a Resolution column in a table where the resolution is space separted, eg: 1024 768.

I want to select all rows that have the width (first number) between A and B.

How can I write something like this in MySQL?

Let's say A is 500 and B is 1200 and resolution is the name of the column.

SELECT * FROM myTable WHERE first_value(resolution) > 500 AND first_value(resolution) < 1200.

What can I use to get the first_value of the space separated string as a number?

Upvotes: 3

Views: 224

Answers (1)

Blue
Blue

Reputation: 22911

You can use SUBSTRING_INDEX() (See this sqlfiddle):

SELECT *
FROM myTable
WHERE SUBSTRING_INDEX(resolution, ' ', 1) > 500 AND SUBSTRING_INDEX(resolution, ' ', 1) < 1200;

Special note: Please be aware that using this method will not be able to utilize any indexes on the table (This query will get much slower as the table grows larger). Because you need to split each row with a function, there is no way this query can use an index to filter this data. Ideally, you should split this data into two separate columns on insert (I'd take a look into using triggers, or possible modifying your code to insert the data differently), and filter the data that way.

Here is how you can do a simple trigger:

CREATE TABLE IF NOT EXISTS `test` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `resolution` varchar(255) NOT NULL,
  `resolution_width` int(11) signed NULL,
  `resolution_height` int(11) signed NULL,
  PRIMARY KEY (`id`) -- DON'T FORGET YOUR INDEX ON resolution_width and resolution_height
                     -- (Or all this will be for naught)
) DEFAULT CHARSET=utf8 //

--Be sure to add a similar one for BEFORE UPDATE if you're updating data as well
CREATE TRIGGER MyTrigger BEFORE INSERT ON `test`
FOR EACH ROW BEGIN
  SET NEW.resolution_width = CAST(SUBSTRING_INDEX(NEW.resolution, ' ', 1) AS SIGNED),
    NEW.resolution_height = CAST(SUBSTRING_INDEX(NEW.resolution, ' ', -1) AS SIGNED);
END//

INSERT INTO `test` (`resolution`) VALUES ('1100 2200');

Upvotes: 2

Related Questions