SQL insert newline / linefeed

[BevanWeiss]

Ok, I can't believe I couldn't just find the answer to this.

I'm trying to add a new line in an SQL string, and getting complaints from the QP about the brackets when trying to use CHR(10).

So far the things I've tried...

CHR(10), CHAR(10), ASCII(10), $L, $0A, \n

The help really wasn't much help on this.

It's such a simple thing, and I'm sure I've done it before without much problem.

I don't think it's a bug, but for the record it's ClearSCADA 2017 May 2018 update.

Help please 🙂

[adamwoodland]

\n works in the column header, not the content, e.g.

SELECT
'Hello' || '\n' || 'Bevan'
FROM
CROOTGROUP

Can't work it out either, pretty sure you used to be able to do something. $n is for logic (and tooltips apparently)

[BevanWeiss]

I'm somewhat glad that puts two of us in the same boat as not quite knowing the solution (right now).

I really feel this would have been an issue sooner if it just wasn't possible though.  Like I really recall having done this several times before, and that like CHR(10) 'just worked'... but it clearly doesn't (at least not for me anymore).

Interesting that '\n' works in the column header.  I would have hoped it was consistent within the Query Processor on the whole.  But I guess not.

@sbeadle  / @JChamberlain  help please 🙂

[JChamberlain]

CHR(10) is supported in expressions, so that might be what you're remembering.

I don't think there is a way to do what you're after but will defer to Steve.

[sbeadle · Accepted solution]

There's no way in the SQL syntax to insert a new line as a literal.

The \n sequence in a column name is one of the special trips in column names to alter the appearance in lists - in this case a multi-row column header. (See help  Core Reference > Coding > SQL Guide > SELECT Clause)

Others are the ~ prefix (fix column in view), _ prefix (hide column) and > prefix (right-justify column content).

Also, there's a way to change the colour of a value according to an RGB value expression (See help  Core Reference > Lists > Custom Lists > Lists in Which only the Entries in Particular Columns Change Color).

I said there's no way, but there always is!

You can create a data table with a row and column containing a \n using a script, then join to that table in queries to output a new line where needed. While ViewX will display a '.' for it, the SQL Export driver will write that new line in.

Sample c# code:

using ClearScada.Client;
// etc etc, and put user and pass into strings
var node = new ClearScada.Client.ServerNode(ClearScada.Client.ConnectionType.Standard, "127.0.0.1", 5481);
ClearScada.Client.Simple.Connection connection = new ClearScada.Client.Simple.Connection("Utility");
connection.Connect(node);
using (var spassword = new System.Security.SecureString())
{
  foreach (var c in pass) { spassword.AppendChar(c); }
  connection.LogOn(user, spassword);
}
ClearScada.Client.Simple.DBObject NewlineTable = connection.GetObject("Test General.Newline");
Object[] p1 = new Object[0];
int record = (int)NewlineTable.InvokeMethod("AddRecord", p1);
Object[] p2 = new Object[3];
p2[0] = "String";
p2[1] = record;
p2[2] = "\n";
NewlineTable.InvokeMethod("SetValue", p2);

[BevanWeiss]

Thanks Steve,

I like the effort with the workaround, but I really don't like the workaround still.

I've just sent off an email to Tech Support to raise an enhancement for it.  I think it would make sense to have the CHR() function available, likely also the ASCII() function.  Although I only care about the CHR() right now, so that's all I had in my request.

I was almost going to suggest, perhaps updating to a more recent 'syntax' of SQL, like ANSI SQL:2011... but I've just had a quite peruse of the standard docs for the updated versions.  They're horrible.  So I think extensions to ANSI-92 is the most sensible way.

For examples of why I consider it horrible (for ClearSCADA use anyway)...

CONSTRUCT
INSERT <pattern> // add new vertices and edges
MERGE <pattern> // add existing and deduplicate elements
MERGE GRAPH <graph> // add all elements from <graph>
DELETE <elements> // delete elements
DETACH DELETE <elements> // delete vertices with their edges
...
RETURN [COPY OF] GRAPH // return projected graph

FROM friends
MATCH (a:Person)-[:TRAVELLED_TOGETHER]-(b:Person)
WHERE a.age = b.age
AND a.country = $country
AND b.country = $country
FROM census($country)
MATCH SHORTEST (a)-[:BORN_IN|MOVED_TO*]->(p)<-[:BORN_IN|MOVED_TO*]-(b)
MATCH (p)-[:LOCATED_IN]->(c:City)
RETURN a.age AS age, c.name AS city, count(*) AS pairs GROUP BY age

 I think it starts to get a bit meta for typical SCADA works..