Add line break to string
-
Hello everyone,
I hope this is the correct spot to post this question.
I have a string that is stored in one of my DB tables that I'm wondering if I could break into separate lines to make it easier to read. We have an alerting system that uses this specific string and because there are no line breaks it makes it difficult to read. Here is an example:
Trigger Conditions: Number of newly found strings 5. Lines that have search string: 2017-11-13 14:18:12,381 ERROR [ajp-127.0.0.1-8009-2] This is a test ; 2017-11-13 14:18:12,381 ERROR [ajp-127.0.0.1-8009-2] Another test line ; 2017-11-13 14:18:12,381 ERROR [ajp-127.0.0.1-8009-2] Generic log event ; 2017-11-13 14:18:12,381 ERROR [ajp-127.0.0.1-8009-2] Another line of text ; 2017-11-13 14:18:12,365 ERROR [ajp-127.0.0.1-8009-2] The end ;
Is there a way to force a new line after a specific character, for instance, the ";" character? Or maybe another way of doing this with a SELECT statement? Ideally, I would like the output to look like this:
Trigger Conditions: Number of newly found strings 5. Lines that have search string:
2017-11-13 14:18:12,381 ERROR [ajp-127.0.0.1-8009-2] This is a test ;
2017-11-13 14:18:12,381 ERROR [ajp-127.0.0.1-8009-2] Another test line ;
2017-11-13 14:18:12,381 ERROR [ajp-127.0.0.1-8009-2] Generic log event ;
2017-11-13 14:18:12,381 ERROR [ajp-127.0.0.1-8009-2] Another line of text ;
2017-11-13 14:18:12,365 ERROR [ajp-127.0.0.1-8009-2] The end ;As a reference, here are the table names:
SELECT StringDataFROM Orion.APM.MultipleStatisticData WHERE ComponentID = "3140"Any help would be awesome, thank you!
-
GAH!!! Comic Sans!!!! MINE EYES!!!!!!!!!!!!! MINE EYES!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Ok, back to normality. CHAR(10) is the Line Break character. So you'll need to use the REPLACE function.
REPLACE(StringData,';',';' + CHAR(10))Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Alternatively you may consider the use of a string splitter, excellently demonstrated by Jeff Moden in his String Splitter article.
NB I have to agree with Thom on the choice of font......................
-
A couple of things. CHAR(10) is the "new line character", or "LINE FEED" (aka LF) from the old days of teletypes. There was also usually CHAR(13) preceding it, which is the "carriage return character", or "CR". Even the VBA language acknowledges this ancient ASCII stuff, and includes a constant named VbCrLf, which represents the combination of both a 13 and a 10, in that sequence. Standard Windows text files use that combination, whereas most Unix-based systems only use 10, so keep that in mind if this data were ever needed to appear in a Windows consumed text file, where adding CHAR(13) + CHAR(10) would be more useful. SSMS will only display the new line, however, when outputting results to Text, and I'm not sure that copying it with just the CHAR(10) in there would allow Excel to see the line breaks in a copy and paste operation.
As to your choice of font, I'm good with it. It is much easier for my old eyes to see... Sorry Thom...
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy) - sgmunson - Tuesday, November 14, 2017 8:25 AM
I've only been using CHAR(10) for years and had no problems with it when copying and pasting from SSMS to any other application (including Excel, Word, Notepad(++), and PoSh and BASH).
sgmunson - Tuesday, November 14, 2017 8:25 AMAnd you sir, are WRONG! :sick: :hehe:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk - Thom A - Tuesday, November 14, 2017 8:29 AMsgmunson - Tuesday, November 14, 2017 8:25 AM
I've only been using CHAR(10) for years and had no problems with it when copying and pasting from SSMS to any other application (including Excel, Word, Notepad(++), and PoSh and BASH).
sgmunson - Tuesday, November 14, 2017 8:25 AMAnd you sir, are WRONG! :sick: :hehe:
I've had to ensure Windows text file format line endings compatibility for so long (40 years now), I can't even conceive of only using CHAR(10) all by itself. As to being WRONG on Comic Sans Ms - feel free to provide me with a better pair of eyes at no cost. :sick: :hehe:
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply