CODEWITHSUNDEEP
mysql
George
George

Reputation: 133

How to use a variable in SELECT AS in MySQL, to specify the displayed column name?

I have here assigned "test123" to the variable @searchstring. I would like the name of the display column to be the value of the variable (ie. test123 in this case).

This is what I issued in MySQL console:

SET @searchstring="test123";  #SET the searchstring you want to search
SELECT DISTINCT(caselist.casenumber1) AS "@searchstring"    FROM caselist LIMIT 1;

This is what I got returned on screen:

+---------------+
| @searchstring |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

The desired result is this:

+---------------+
| test123       |
+---------------+
|             0 |
+---------------+

My actual query issued will be more complex, and refers to the @searchstring multiple times. The content of @searchstring changes, depending on what I want to search. That is why I decided to assign a value to it upfront once. When I want to search other things, I just need to change that variable once upfront.

QUESTION: How do I have the column title to display the content of the variable @searchstring (ie. test123 in this case), rather than the actual name of the variable?

Thank you.

If I do this (ie. remove the quotation marks around the variable name after AS):

SET @searchstring="test123";  #SET the searchstring you want to search
SELECT DISTINCT(caselist.casenumber1) AS @searchstring  FROM caselist ;

Then I get this error message:

mysql> SELECT DISTINCT(caselist.casenumber1) AS @searchstring FROM caselist ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@searchstring FROM caselist' at line 1
mysql>

Upvotes: 1

Views: 32

Answers (1)

lemon
lemon

Reputation: 15482

One option is using dynamic querying for this problem:

SET @searchstring = "test123";  #SET the searchstring you want to search

SET @sql = CONCAT('SELECT DISTINCT casenumber1 AS ',
                  @searchstring,
                  ' FROM caselist LIMIT 1');

PREPARE stmt FROM @sql;
EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Check the demo here.

Upvotes: 3

Related Questions