How to combine CONCAT() and WHERE in MariaDB?

Question

I like to use the result from another query to address the column name. For this I like to use CONCAT(). But somehow it don't work; when I run this line I get 0 rows back:

SELECT * FROM cover WHERE CONCAT('c','9') = 1;

When I don't make use of CONCAT() it work perfectly:

SELECT * FROM cover WHERE c9 = 1;

And also CONCAT() seems to work. With this I get a result:

SELECT CONCAT('c','9');

I tried all solution from this question: MySQL select with CONCAT condition

like this one, but i always got 0rows back:

SELECT * FROM (
  SELECT id, CONCAT('c', '9') as target 
  FROM cover) base 
WHERE target = "1"

My MySQL Version is; 10.1.16-MariaDB

`concat` produces a string, which is not interpreted as the name of a column. If you could use a program (eg php, perl, python) to build the column name dynamically, that'd be easier. — Déjà vu, Aug 27 '17 at 21:53
@ringø thanks, i will consider to do it in php. but in this question: https://stackoverflow.com/questions/5734570/mysql-select-with-concat-condition it look like that it is possible. — and-bri, Aug 27 '17 at 21:59
ok thanks for the explanation...i will go with php ;) ...and yes, I want to make a column name. — and-bri, Aug 27 '17 at 22:05

Rick James · Accepted Answer · 2017-08-28T18:03:54.957

4

It is bad schema design to splay an array across a bunch of columns. And, as you are discovering, it is hard to use the columns. Build another table for the c values.

Or...

With lots of 0/1 "columns", consider SET or BIGINT UNSIGNED; either will hold up to 64 boolean flags in a tiny fraction of the space. And, with different code, BLOB could be used.

To extract bit 22 from a BIGINT, ((col >> 22) & 1) will give you 0 or 1.

edited Aug 28 '17 at 18:03

answered Aug 27 '17 at 23:57

Rick James

135,179
13
127
222

Interesting. The table i like to design have about 100k rows and between 100 and 1000 columns. I have to fill every field with a 1 or 0. For me this looks like a perfect case for use a matrix. You tell me it is more efficient to create a table with 3 columns (the both dimensions and the value) which is between 10M and 1000M rows long?? – and-bri Aug 28 '17 at 06:02
I augmented my answer. Could you provide more details on how the data will be used? Are you looking for c9 to "filter" or to "compute" or what? – Rick James Aug 28 '17 at 18:05
Thanks so far...I think it is better when i open a new question, because this one seems not a good place to discus this issue. – and-bri Aug 28 '17 at 18:17

score 2 · Answer 2 · answered Aug 27 '17 at 22:10

Consider using a case when, since the number of options is known beforehand (you can only access columns that exist):

SELECT id 
FROM   cover 
WHERE  case ? 
           when 1 then c1
           when 2 then c2
           when 9 then c9
       end = 1

... where the question mark would be the provided value, like 9 in your example.

How to combine CONCAT() and WHERE in MariaDB?

2 Answers2