mysql SELECT from column with a concatenated string

Question

I select something from a column named "9 stars" (without the "").

SELECT `9 stars` FROM `table` WHERE ...

works fine, but I have to find a solution to do the job combining a variable (i = 9) with the "stars", something like:

i = 9;  
SELECT `i stars` FROM `table` WHERE ...  (which does not work like this, of course).

What environment are you in? How is this query getting to MySQL? — miken32, Jan 21 '16 at 17:43
Possible duplicate of [Dynamic conversion of string into column name. MySQL](http://stackoverflow.com/questions/13282718/dynamic-conversion-of-string-into-column-name-mysql) — miken32, Jan 21 '16 at 17:51
Your question is very unclear on what you have and what you need leading to divergent answers. — El Gucs, Jan 21 '16 at 18:08

score 4 · Answer 1 · edited May 23 '17 at 11:52

4

If you want to do it in pure Sql then For Dynamic Column name you need to use prepare statement

SET @i =9;
SET @table = <tblname>;
SET @query = CONCAT('SELECT ',CONCAT('`',@i,' ', 'start','`'),' FROM ', @table);

PREPARE stmt FROM @query;
EXECUTE stmt;

check this

Dynamic conversion of string into column name. MySQL

edited May 23 '17 at 11:52

Community

1
1

answered Jan 21 '16 at 17:47

developerCK

4,418
3
16
35

1

Why you nested `CONCAT` ? – Juan Carlos Oropeza Jan 21 '16 at 18:00
just in case to show him/her that we can make a dynamic query by multiple variable and then we can execute. – developerCK Jan 21 '16 at 18:04
3

@JuanCarlosOropeza that is a little odd, but should not affect the results; but you did make me notice that developerCK should be including `\`` around the field. `SELECT 9 start from sometable` is not going to work as needed. – Uueerdo Jan 21 '16 at 18:04
1

I mean you can concat multiple variables in a single `CONCAT` so no need to nested. Or is there a limit on the number of variables? – Juan Carlos Oropeza Jan 21 '16 at 18:11
A simpler `CONCAT` would be: `CONCAT('SELECT \`',@i,' stars\` FROM ', @table);` – El Gucs Jan 21 '16 at 18:28

score 3 · Accepted Answer · answered Jan 21 '16 at 18:00

3

If you don't want to mess around with dynamically constructing your query AND know how many "i" values there are, you could just do this:

SET @i = whatever;
SELECT CASE @i 
   WHEN 1 THEN `1 stars`
   WHEN 2 THEN `2 stars`
   WHEN 3 THEN `3 stars`
   WHEN 4 THEN `4 stars`
   WHEN 5 THEN `5 stars`
   WHEN 6 THEN `6 stars`
   etc....
   ELSE NULL
END AS `the_stars`
FROM table
WHERE ...

You could also use the value from another field of the row instead of a session variable (@i)

answered Jan 21 '16 at 18:00

Uueerdo

15,723
1
16
21

I was going to write this one too, but eating my fruits now :) To be clear because the format doesnt show it. `1 star` is a field name and will return that content – Juan Carlos Oropeza Jan 21 '16 at 18:05
Question didn't make it clear how consistent the field naming was, so I went with uniformity for the sake of the example. – Uueerdo Jan 21 '16 at 18:07
@Uueerdo, in this particular case, Your approach seems to be Best.out of the box solution, Great Man! (We should make thing simpler not Complex!) – developerCK Jan 21 '16 at 18:11
btw I think you mean `user variable` not `session variable` – Juan Carlos Oropeza Jan 21 '16 at 18:15
This would be good for a limited number of columns, but could get messy real fast if there were more than 5 or 6. – miken32 Jan 21 '16 at 18:24
@JuanCarlosOropeza I am fairly certain I mean `session`. I believe all single `@` variables are connection(session) specific; I think it is possible to have `global` user variables with `@@`, but am not positive since I have little use for them. – Uueerdo Jan 21 '16 at 18:50
@miken32 agreed on the feasibility of this kind of solution decreasing with the number of fields chosen in this way; in extreme scenarios the query could actually exceed max length allowed. With fewer fields, this format can be clearer, especially if using another field as the case subject instead of a variable (dynamic sql would involve executing separate queries for each value, or potentially massive unions of such queries.) – Uueerdo Jan 21 '16 at 18:51
Great, I got it solved with the CASE construction. Maybe not the most universal way for all needs in the future but in my particular case just perfect. Thanks a lot! – user5564160 Jan 22 '16 at 08:30

score 1 · Answer 3 · answered Jan 21 '16 at 17:32

1

Use CONCAT

SET @i = 9;
SELECT CONCAT(@i,  'stars') 
FROM table WHERE ...

answered Jan 21 '16 at 17:32

Juan Carlos Oropeza

47,252
12
78
118

I tried that but as a result of the SELECT I do not get the value of the row but "9 stars" (without "") – user5564160 Jan 21 '16 at 17:41
what you mean without the "" ? The result is a string – Juan Carlos Oropeza Jan 21 '16 at 17:55
@JuanCarlosOropeza he wants to dynamically select the **field** he is selecting. – Uueerdo Jan 21 '16 at 17:57
@Uueerdo I know dynamic query, but dont see that in the question, maybe Im too hungry :( – Juan Carlos Oropeza Jan 21 '16 at 17:58
@Uueerdo Ok I see it now. – Juan Carlos Oropeza Jan 21 '16 at 17:59
The question is unclear about many things. One of those is that he needs a value of a row in the concatenated string. Just add the field to the `CONCAT` statement – El Gucs Jan 21 '16 at 18:00
CONCAT() returns a string, equivalent to `SELECT "9stars" FROM table`. It can not not be used for a column name. This answer should be deleted so as not to mislead future visitors. – miken32 Jan 21 '16 at 18:21

score 0 · Answer 4 · answered Aug 11 '21 at 12:49

0

You could use the ELT function in MySQL :

SET @i = 3;
SELECT ELT(@i, `1 stars`, `2 stars`, `3 stars`, `4 stars`[, ...]) FROM `table` WHERE [...];

answered Aug 11 '21 at 12:49

rhessus

16
2

mysql SELECT from column with a concatenated string

4 Answers4