Blogs Home
Tuesday, November 24, 2020 12:00 PM

T-SQL- Reformatting bulk text into a table of lines

Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems

TSQL Reformatting - Blog Header

MEDITECH stores a lot of bulk text in the DR; usually as a sequenced table of lines, with some line length. That text may or may not be formatted with extra characters to control fonts and bold/italic/etc.

Often, however you will want to collect up that text from its table of lines and recombine it into a single long VARCHAR(MAX) field to display on a report. That is fine, you do something like:

Bulk Text image #1

And then reference IRT.TextLines to get your combined text field.

However, you may also find yourself needing to take the line-sequenced chunks of text, combine them, and then output them into a new line-sequenced structure with a different line length!

Oy! What then? You need a handy function to take the single mass of text and output it into a new table of sequenced lines of text, with the words properly separated into the new line length!

Something like this! [ IatricRaggedRightFt ]

Now you can take the same OUTER APPLY / FOR XML approach, combine the text, then output it:

Bulk Text image #2

Stack them in order in your JOIN / OUTER APPLY section of your code and you can swap one to another…


Extra Credit

Incorporate another function to strip out the MEDITECH text formatting code!


As ever, if you need help with MEDITECH DR optimization, reporting, extracting, index creation or analysis please feel free to give your iatricSystems Account Executive a call or email to discuss how we can help support your team!