Break line on varchar string when delimeter SQL

Question

i have SQL SELECT query with long string, example:

SELECT RTRIM(CAST(DNH_INFO AS VARCHAR(150)))

'_This is _a very _long string _separated _with underline'

as i result a want to break lines by underline but still remain the same string, exmaple:

'_This is

 _a very

 _long string

 _...'

Is there any SQL function with which i can solve this problem?

Thanks for help!

I think you should mark SQL db engine (MSSQL, MySQL, Oracle, Postresql, DB2, Access, etc.) — etsa, Jun 22 '17 at 07:18
...then you can give a look at https://stackoverflow.com/questions/5123585/how-to-split-a-single-column-values-to-multiple-column-values or similar — etsa, Jun 22 '17 at 07:19
Yes but this is not what i want. I want is as a one rown just with line breaks. NOT different column. — makaroN, Jun 22 '17 at 07:25
The solution would be different for different RDBMS, so it is important to mention just one. — trincot, Jun 22 '17 at 07:27

score 1 · Accepted Answer · edited Jun 22 '17 at 07:33

1

@makaroN you can replace the space and underline with a LineFeed and Carriage Return and then re-add the underline. Alternatively you can use just the carriage Return (char(13) or reverse char(13) + char(10) if you do not need the extra line space. See the example below.

SELECT REPLACE('_This is _a very _long string _separated _with underline', 
               ' _', char(10) + char(13) + '_')

p.s if executing in management studio you may want to select Results to Text to immediately see the format.

edited Jun 22 '17 at 07:33

trincot

317,000
35
244
286

answered Jun 22 '17 at 07:31

Richard Mohammed

84
4

Thanks @trincot was just editing to show the underscore. – Richard Mohammed Jun 22 '17 at 07:35
Windows newline is CrLf, not LfCr, so I'd say you need `char(13) + char(10)` to get a line break. – Andre Jun 22 '17 at 07:37
Yep it is which I mentioned, but his example shows a large space between the lines which you'd get by reversing to LfCr... at least on management studio. – Richard Mohammed Jun 22 '17 at 07:39

Break line on varchar string when delimeter SQL

1 Answers1