How to induce Line breaks in SQL Query Output

Question

I have a select query in Synapse Dedicated SQL pool returning below output -

21:40,21:41,22:01,22:07,22:12

Instead I want the output to return as below -

21:40

21:41

22:01

22:07

22:12

How can I achieve this on Synapse Dedicated SQL Pool? I tried all below but nothing works

SELECT 'First line.'+ CHAR(10) + 'Second line.' AS 'New Line'

SELECT 'First line.'+ CHAR(13) + 'Second line.' AS 'New Line'

SELECT 'First line.'+ CHAR(13)+CHAR(10) + 'Second line.' AS 'New Line'

SELECT 'First line.
Second line.' AS 'New Line'

Answer 1

Hi Vivek Komarla Bhaskar,

Thank you for posting query in Micrsoft Q&A Platform.

CHAR(13) gives us line break in SQL and to sense it we need to switch to results in text. In SSMS results in text is available, whereas in Azure data studio saving results to text is not available at this moment. But still, you can save results to markdown in Azure data studio and find tag <br /> which hints presence of line break.

For some reason, if we want to save it or view it as text then kindly use SSMS in this case.

Kindly check below images for better understanding.

SSMS, here I am switching to results to text to visually sense line break

Azure data studio, here there is not option to save results to text. Hence saved as markdown and checking for line break tag to make sure line break.

Kindly check below screenshot which says, results to text is not available in Azure data studio. Click here for more details.

Hope this helps.

---

Please consider marking it as Accepted Answer and click on yes if its helpful. Please note, accepted answers help community as well.

Answer 2

Hello @Vivek Komarla Bhaskar,

Welcome to the MS Q&A platform.

Table syntax:

create table table_name
(column_name varchar(50))

 Insert statement:

insert into table_name values ('21:40,21:41,22:01,22:07,22:12')

 Use the below query, to get your desired output

select REPLACE(column_name, ',', CHAR(13)) from table_name

Output:  

21:40
21:41
22:01
22:07
22:12

for the carriage return(line break), please use the below query
select REPLACE(column_name, ',', CHAR(10) + CHAR(13)) from table_name

Output:  

21:40

 

21:41

 

22:01

 

22:07

 

22:12

 

Please note:
By default, SSMS does not retain SQL carriage return on copy/save. We need to enable “Retain CR/LF on copy or save" from the options tab.

Please follow the below blog, which explains SQL carriage return, Tab, and Line break functions.
https://www.sqlshack.com/sql-carriage-return-or-tab-in-sql-server-string/#:~:text=Inserting%20line%20break%20or%20new%20line&text=We%20can%20replace%20the%20comma,10)%20inserts%20a%20line%20break.%20inserts%20a%20line%20break.)

I hope this helps. Please let us know if you have any further questions.

Answer 3

You can use the REPLACE() function to replace the comma with a space. You can use the following query:

SELECT REPLACE(colname, ',', ' ') FROM tablename

You can replace "colname" with the name of the column that contains the original data, and "tablename" with the name of the table that contains the data. This will replace all commas with spaces in that column, resulting in the desired output.