How to have a newline in a string from a custom SQL query on an EA template fragment?

Question

I need to output from a custom SQL query a string that includes a newline into a custom field of a fragment template. I was hopping that something like this would work:

Select concat('\nHello World', field1) from table1

... except is doesn't. I just get the '\nHello World' string in the doc without the line break. I also already tried with char(10), char(13) and a lot of other (instead of \n) and the result is the same.

How can I add a newline at the beginning of my field in an SQL fragment template?

Answer 1

CHAR(10) is a line break and CHAR(13) is a carriage return in SQL. '\n' is just "{backslash}n", it doesn't mean a new line. You would want:

SELECT CONCAT(CHAR(13),CHAR(10),'Hello World', field1)
FROM table1;

db<>fiddle

Answer 2

SQL Fragment

You can add newlines in an SQL Fragment on SQL Server using CHAR(10) in your query.

But EA helpfully trims your output and removes any and all whitespace at both ends of your field.

select ' ' + CHAR(10) + 'line 1' + CHAR(10) + 'line 2'  + CHAR(10) + ' ' as test

is stripped from leading and trailing whitespace resulting in just

line 1 
line 2 

The same happens with the TAB character.

Script fragment

An alternative to SQL fragments are Script fragments. With these fragments you return an XML string that is that printed in your template.

Unfortunately the exact same thing happens with Script fragments. EA still trims leading and trailing whitespace from the fields.

Document script fragment

As a last attempt to beat EA and keep the elusive whitespace in the template I tried a Document Script Fragment. This is a new(ish) feature (introduced in v13 or v14 I believe) where you call a script that returns raw RTF code.

So what I did was open up WordPad and type the content I needed to show up in the template

Then saved it as .rtf and opened the file with notepad and copied the rtf code

{\rtf1\ansi\ansicpg1252\deff0\nouicompat\deflang1036{\fonttbl{\f0\fnil\fcharset0 Calibri;}}
{\*\generator Riched20 10.0.18362}\viewkind4\uc1 
\pard\sa200\sl276\slmult1\f0\fs22\lang9\par
\par
line 1\par
line 2\par
\par
}

Notice the two \par before the start of line 1

Then I create a script called DocumentScriptFragment in EA to return this exact string

option explicit

!INC Local Scripts.EAConstants-VBScript

'
' Script Name: DocumentScriptFragment
' Author: Geert Bellekens
' Purpose: test a document script fragment by returning raw RTF
' Date: 2020-11-27
'
function MyRTFData()
    MyRTFData = "{\rtf1\ansi\ansicpg1252\deff0\nouicompat\deflang1036{\fonttbl{\f0\fnil\fcharset0 Calibri;}}" & vbNewLine & _
                "{\*\generator Riched20 10.0.18362}\viewkind4\uc1" & vbNewLine & _
                "\pard\sa200\sl276\slmult1\f0\fs22\lang9\par" & vbNewLine & _
                "\par" & vbNewLine & _
                "line 1\par" & vbNewLine & _
                "line 2\par" & vbNewLine & _
                "\par" & vbNewLine & _
                "}" & vbNewLine
end function

Created a document script template fragment that calls my script

And finally a template that uses my fragment

Using this template resulted finally in a document containing this so much needed leading newline

Notice however how only one of the \par survived. So always add an extra \par at the beginning of your RTF.

Conclusion

• Is it possible to add a leading newline in an SQL fragment? NO
• Is it possible to use a Script fragment as an alternative? NO
• Is it possible to use a Document script fragment as an alternative? YES as long as you add an extra \par at the beginning of your rtf code.