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
– rene, Commented Nov 25, 2012 at 12:36

Community · Accepted Answer · 2017-05-23 11:47:23Z

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, 2017 at 11:47

CommunityBot

11 silver badge

answered Nov 25, 2012 at 12:37

Ben Lee

53.5k15 gold badges129 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SaidbakR Over a year ago

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.

Ben Lee Over a year ago

@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).

Joni · Accepted Answer · 2012-11-25 12:49:34Z

27

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.

answered Nov 25, 2012 at 12:49

Joni

112k14 gold badges151 silver badges201 bronze badges

Comments

Randell · Accepted Answer · 2014-04-10 01:12:53Z

2

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

edited Apr 10, 2014 at 1:12

Randell

6,1716 gold badges47 silver badges71 bronze badges

answered Nov 25, 2012 at 12:36

rekire

48.1k34 gold badges184 silver badges279 bronze badges

Comments

Rahul Tripathi · Accepted Answer · 2012-11-25 12:38:11Z

1

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

answered Nov 25, 2012 at 12:38

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

1 Comment

mickmackusa Over a year ago

Researchers shouldn't need to click a link to understand your advice.

Collectives™ on Stack Overflow

Concat a string to SELECT * MySql

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related