SELECT and CONCAT in MySQL-CodePudding

I'm using MySQL and trying to use CONCAT_WS() function with SELECT.

I tried:

SELECT id, A_12, CONCAT_WS('A_', '12') as testA from TABLE_A

I expected something like

id | A_12 | testA |
-------------------
 1 |  20  |   20  |

The value is testA should be same as the value in A_12.

However, what I'm getting is

id | A_12 | testA |
-------------------
 1 |  20  |   12  |

The '12' in testA column is simply coming from latter string of the CONCAT_WS() function CONCAT_WS('A_','12').

Any help would be appreciated.

======EDIT======:

Sorry I didn't clearly state my question and purpose in the beginning. I have 12 columns A_1, A_2, ... , A_12 in TABLE_A. More specifically, Table_A looks like this:

id | A_1 | A_2 | ... | A_12|
---------------------------
 1 |  4  |  5  | ... |  20 |
 2 |  1  |  4  | ... |  50 |
 3 |  2  |  5  | ... |  70 |

I also have another table TABLE_B that looks something like this:

id | value
----------
 1 | 12
 2 | 5
 3 | 3

I'm trying to create a stored function that...

select the corresponding value from TABLE_B
from TABLE_A, pull info under the column A_ the value from Table_B

for every id.

So I have

SELECT id, CONCAT_WS('A_', stored-value-from-TABLE_B) as testA from TABLE_A

To make sure if the code is running as I expect, I ran

SELECT id, A_12, CONCAT_WS('A_', '12') as testA from TABLE_A

since the value for id=1 in Table_B is 12.

However, what I'm getting is 12 in testA column for every id.

CodePudding user response：

well to get this result just need

SELECT id, A_12, A_12 as testA from TABLE_A

CodePudding user response：

You seem to think that a select-list is bound to be a list of column names, therefore a string function like CONCAT_WS() would produce a string like A_12 and that string must be interpreted as a column identifier, so the result of that query would use the value of the column named by that string.

But that's not how an SQL select-list works.

A select-list is a list of expressions. You could use a simple column name, and the result would be the value in that column. Or you could use another expression, in this case a string function, and the result will be the string returned by that function — not the column that coincidentally has a name matching that string.

As mentioned in the comment above, identifiers are fixed in an SQL query. You cannot make a string expression and have the value of that expression be interpreted as an identifier in the same query. To make a dynamic reference to an identifier, you need to format it in your SQL syntax before you prepare the query.

You also misunderstand what CONCAT_WS('A_', '12') does. It concatenates its 2nd argument and further arguments, with a separator between them of the 1st argument. A typical usage would be:

CONCAT_WS(', ', col1, col2, col3, ...)`

This returns a list of comma-separated words from the values of several columns: "value1, value2, value3".

So in your case, you concatenated a single value "12" but the separator "A_" does not appear because there is only one value in the list.

CodePudding user response：

You can join the tables on their ids and pick the proper A_? with the function ELT() where you have to enumerate all 12 columns:

SELECT b.*, 
       ELT(b.value, a.A_1, a.A_2, ..., a.A_12) testA
FROM TABLE_B b INNER JOIN TABLE_A a
ON b.id = a.id;

See a simplified demo.

CodePudding user response：

First, you don't want CONCAT_WS(), you want CONCAT() - Concat with separator inserts whatever your first argument is between all the others, and since you only have one other argument, it never gets used - for example CONCAT_WS('A_', '12', '13') would give you 12A_13. CONCAT('A_', '12') gives you A_12 but as a string, not as a column name.

After correcting to CONCAT and evaluating, your select will look like this SELECT id, A_12, 'A_12' as testA from TABLE_A; Notice the quotes around A_12.

This is because concat functions return a string and can't be used to build a column name in a select string the way you want. It's possible to do so, but is complicated - you would have to build your entire query string in a string variable then execute it as a prepared statement:

SET @QueryString = CONCAT('SELECT id, A_12, ', CONCAT('A_', '12'), ' as testA from TABLE_A;');
PREPARE stmnt FROM @QueryString;
EXECUTE stmnt;

The nested CONCAT() is unnecessary, since concat can take any number of arguments so you get it simplified to this:

SET @QueryString = CONCAT('SELECT id, A_12, ', 'A_', '12', ' as testA from TABLE_A;');
PREPARE stmnt FROM @QueryString;
EXECUTE stmnt;

And your @QueryString will be SELECT id, A_12, A_12 as testA from TABLE_A;

This can be pretty dangerous if anything in that string comes from user input in a system. If this is connected to any application, combine the strings using whatever concatenation your server side application language uses then execute that as a query.

DB Fiddle testing (SQLFiddle seems to have removed support for selects in prepared statements)