Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to combine multiple columns as one and format with custom strings?

Tags:

select

mysql

SELECT id, <X> AS name FROM `table` 

Basically <X> is a combination of
lastname + ', ' + firstname

example would be

   id | name        |    2  | Smith, Bob  |    3  | Jones, Susy | 

This is just an example, I don't really want to combine names so simple.

like image 1000
Moak Avatar asked Sep 21 '10 06:09

Moak


People also ask

How do I combine columns of string in R?

How do I concatenate two columns in R? To concatenate two columns you can use the <code>paste()</code> function. For example, if you want to combine the two columns A and B in the dataframe df you can use the following code: <code>df['AB'] <- paste(df$A, df$B)</code>.

How do I concatenate text from multiple rows into a single text string in MySQL?

The GROUP_CONCAT() function in MySQL is used to concatenate data from multiple rows into one field. This is an aggregate (GROUP BY) function which returns a String value, if the group contains at least one non-NULL value.


1 Answers

What about the CONCAT() function?

SELECT id, CONCAT(lastname, ', ', firstname) AS name FROM `table`; 

If you are going to concatenate many fields, you could also consider the CONCAT_WS() function, where the first argument is the separator for the rest of the arguments, which is added between the strings to be concatenated:

SELECT id,         CONCAT_WS(',', field_1, field_2, field_3, field_4) list FROM   `table`; 
like image 161
Daniel Vassallo Avatar answered Sep 22 '22 10:09

Daniel Vassallo