SELECT and CONCAT in MySQL

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.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

well to get this result just need

SELECT id, A_12, A_12 as testA from TABLE_A

Method 3

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.

Method 4

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)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating