Concat a string to SELECT * MySql

Question

The following query works fine with MySQL:

SELECT concat(title,'/') FROM `socials` WHERE 1

It Concat / to the selected title field.

However, when I try to do the following:

SELECT concat(*,'/') FROM `socials` WHERE 1

It returns the follwoing Error:

 #1064 - 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 '*,'/') FROM `socials` WHERE 1 LIMIT 0, 30' at line 1

So is there any way to make such sql query to work with MySql

No, that can't be done in a plain select. Can you use a stored procedure? — rene, Nov 25 '12 at 12:36

score 49 · Accepted Answer · edited May 23 '17 at 11:47

49

You simply can't do that in SQL. You have to explicitly list the fields and concat each one:

SELECT CONCAT(field1, '/'), CONCAT(field2, '/'), ... FROM `socials` WHERE 1

If you are using an app, you can use SQL to read the column names, and then use your app to construct a query like above. See this stackoverflow question to find the column names: Get table column names in mysql?

edited May 23 '17 at 11:47

Community

1
1

answered Nov 25 '12 at 12:37

Ben Lee

52,489
13
125
145

Thank you all, but the most distinguishable aspect of this answer to obtain it as correct, is showing how to achieve selecting the multiple fields. – SaidbakR Nov 25 '12 at 12:42
1

@sємsєм, you do it by listing them manually (or, if you are using an app, you can use SQL to read the columns, and then use your app to construct a query like above). – Ben Lee Nov 25 '12 at 12:45

score 27 · Answer 2 · answered Nov 25 '12 at 12:49

If you want to concatenate the fields using / as a separator, you can use concat_ws:

select concat_ws('/', col1, col2, col3) from mytable

You cannot escape listing the columns in the query though. The *-syntax works only in "select * from". You can list the columns and construct the query dynamically though.

score 2 · Answer 3 · edited Apr 10 '14 at 01:12

2

You cannot concatenate multiple fields with a string. You need to select a field instand of all (*).

edited Apr 10 '14 at 01:12

Randell

6,112
6
45
70

answered Nov 25 '12 at 12:36

rekire

47,260
30
167
264

score 1 · Answer 4 · answered Nov 25 '12 at 12:38

1

You cannot do this on multiple fields. You can also look for this.

answered Nov 25 '12 at 12:38

Rahul Tripathi

168,305
31
280
331

Researchers shouldn't need to click a link to understand your advice. – mickmackusa Oct 31 '22 at 04:00

Concat a string to SELECT * MySql

4 Answers4