CODEWITHSUNDEEP
mysqlsqlstringconcatenation
SaidbakR
SaidbakR

Reputation: 13544

Concat a string to SELECT * MySql

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

Upvotes: 33

Views: 164553

Answers (4)

rekire
rekire

Reputation: 47945

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

Upvotes: 2

Joni
Joni

Reputation: 111259

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.

Upvotes: 27

Ben Lee
Ben Lee

Reputation: 53319

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?

Upvotes: 49

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

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

Upvotes: 1

Related Questions