How do you append a carriage return to a value in MySQL?

MySQLMySQLi Database

You need to use CONCAT_WS() function from MySQL to append a carriage return. If you are looking for a new line, then append \n in the beginning. The syntax is as follows −

SELECT CONCAT_WS(‘\n’,yourColumnName) as anyVariableName from yourTableName;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table NewLineDemo
-> (
-> CountryName varchar(10)
-> );
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into NewLineDemo values('US');
Query OK, 1 row affected (0.15 sec)
mysql> insert into NewLineDemo values('UK');
Query OK, 1 row affected (0.13 sec)
mysql> insert into NewLineDemo values('AUS');
Query OK, 1 row affected (0.11 sec

Display all records from the table using select statement. The query is as follows:

mysql> select *from NewLineDemo;

The following is the output −

+-------------+
| CountryName |
+-------------+
| US          |
| UK          |
| AUS         |
+-------------+
3 rows in set (0.00 sec)

Here is the query for a list of values using CONCAT_WS(). In this function the first parameter will be ‘\n’ for new line. The query is as follows:

mysql> select concat_ws('\n',CountryName) as CountryList from NewLineDemo;

The following is the output −

+-------------+
| CountryList |
+-------------+
| US          |
| UK          |
| AUS         |
+-------------+
3 rows in set (0.00 sec)

Or you can understand the above query like this. If all values are separated with comma, the query is as follows −

mysql> select concat_ws('\n','US','UK','AUS') as CountryName;

The following is the output −

+-------------+
| CountryName |
+-------------+
| US          |
| UK          |
| AUS         |
+-------------+
1 row in set (0.00 sec)
raja
Published on 26-Feb-2019 12:16:42
Advertisements