MySQL first digit from integer in WHERE clause

Question

I'm looking around to find this solution, but nothing's coming up.

I have categories and subcategories. They are represented by integers like so:

1 -> high level
    10 -> sub
    11 -> sub
    12 -> sub
2 -> high level
    20 -> sub
    21 -> sub

...etc. So to search on the high level categories and have it come back with its subcategories, I need to search on the first digit. First guess is to cast it to a string and search its substring. Is there a more concise way though?

_“I need to search on the first digit”_ – do you? At least from your sample data, it looks like your sub-categories can be no more than 10 each, so filtering for `>= 10 AND <= 19` should work … — CBroe, Aug 05 '14 at 14:19
Did all subcategories have only 2 digits ? if so `xy / 10` gives you the `x`. — webNeat, Aug 05 '14 at 14:24

score 22 · Accepted Answer · answered Aug 05 '14 at 14:37

22

If category ID's have an arbitrary number of digits, you can also do it with a little bit of maths:

SELECT CAST(id / POW(10, CAST(LOG10(id) AS SIGNED)) AS SIGNED)

;)

Just kidding, go for LEFT(id, 1).

Or MID(id, position, 1), to retreive the other digits.

answered Aug 05 '14 at 14:37

RandomSeed

29,301
6
52
87

Hahah got a good chuckle, thanks! For now I'll probably go with this way for now – coleman-benjamin Aug 05 '14 at 14:40
2

@Meowts Please note, this approach does not scale well. A search with `WHERE id LIKE '1%'`, [as suggested by Marcus Adams](http://stackoverflow.com/a/25141119/1446005) will be able to use an index, whereas `WHERE LEFT(id, 1)` will not. But it is not possible to use an index to retreive (e.g.) the second digit (e.g. `WHERE id LIKE '?1%'`). There are [many other, smarter ways](http://stackoverflow.com/q/4048151/1446005) to represent hierarchical data in a RDBMS. – RandomSeed Aug 05 '14 at 14:48

Marcus Adams · Answer 2 · 2014-08-05T14:32:21.807

You can use the LEFT() function to return the left most character:

SELECT LEFT(category, 1)

MySQL will automatically cast the column value to a string if it's an integer.

You can also use a wildcard and LIKE for comparison:

SELECT category LIKE '1%'

However, if the category is treated as a string, consider making it a string, then MySQL can utilize an index on category in the LIKE example when selecting like this:

SELECT * FROM categories WHERE category LIKE '1%'

score 1 · Answer 3 · answered Aug 05 '14 at 14:30

You can use like on integers too :

MariaDB [sometest]> desc cat;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| lib   | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

MariaDB [sometest]> select * from cat where id like '1%';
+------+------------+
| id   | lib        |
+------+------------+
|    1 | high level |
|   10 | 1 sub 0    |
|   11 | 1 sub 1    |
|   12 | 1 sub 2    |
+------+------------+
4 rows in set (0.00 sec)

MariaDB [sometest]>

However, I hope you wont ever have more that 10 categories our sub-categories. You probably should revisit your table layout, and use self-referencing records :

MariaDB [sometest]> create table self (id int not null auto_increment primary key, parent_id int, lib varchar(255));
Query OK, 0 rows affected (0.01 sec)

MariaDB [sometest]> insert into self values ('', '', 'high level 1');
Query OK, 1 row affected, 2 warnings (0.00 sec)

MariaDB [sometest]> insert into self values ('', '', 'high level 2');
Query OK, 1 row affected, 2 warnings (0.00 sec)

MariaDB [sometest]> select * from self;
+----+-----------+--------------+
| id | parent_id | lib          |
+----+-----------+--------------+
|  1 |         0 | high level 1 |
|  2 |         0 | high level 2 |
+----+-----------+--------------+
2 rows in set (0.00 sec)

MariaDB [sometest]> insert into self values ('', '1', 'sub level 1');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [sometest]> insert into self values ('', '1', 'sub level 2');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [sometest]> insert into self values ('', '1', 'sub level 3');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [sometest]> select * from self where id=1 or parent_id = 1;
+----+-----------+--------------+
| id | parent_id | lib          |
+----+-----------+--------------+
|  1 |         0 | high level 1 |
|  3 |         1 | sub level 1  |
|  4 |         1 | sub level 2  |
|  5 |         1 | sub level 3  |
+----+-----------+--------------+
4 rows in set (0.00 sec)

this way, you lift the 10 limit, and have more flexible search possibilities. My intuitive guess is also that searching will be much faster (you should extend this trivial example to add an index on parent_id at least).

Thanks, yeah the matter of having more than 10 sub categories is a question, for this version it's not a huge concern though, but thanks for the tip! — coleman-benjamin, Aug 05 '14 at 14:42

MySQL first digit from integer in WHERE clause

3 Answers3