Breaking a String Across Multiple Lines

Question

I wrote a script which is connecting to an Oracle database to select multiple entries in a specific table.

The statement looks like this:

rs.open "SELECT PATH301 FROM NC301B WHERE EDIPROC like 'P30_' AND (LF301M > 0) AND (PATH301 NOT LIKE '%saptemp%') AND (PATH301 NOT LIKE '%SAPTEMP%') AND (PATH301 NOT LIKE '%usr%') AND (PATH301 NOT LIKE '%Windows%');", cn, 3

Now I want to know if it is possible to split this statement over multiple lines. For example like this:

rs.open "SELECT PATH301 FROM NC301B 
WHERE EDIPROC like 'P30_' 
AND (LF301M > 0) 
AND (PATH301 NOT LIKE '%saptemp%') 
AND (PATH301 NOT LIKE '%SAPTEMP%') 
AND (PATH301 NOT LIKE '%usr%') 
AND (PATH301 NOT LIKE '%Windows%');", cn, 3

That first SELECT Statement is way to big and not looking good at all. I hope you understand what I mean.

If you mean just make the command easier to read you want the continuation character _. — user692942, Commented Jun 1, 2016 at 9:40
Possible duplicate of Splitting IF statement in multiple lines in VBScript — user692942, Commented Jun 1, 2016 at 9:51
Not really a duplicate of that question. The issue here is two-fold, since the OP wants to wrap a string and VBScript doesn't support multiline strings. — Ansgar Wiechers, Commented Jun 1, 2016 at 10:01
Still another stackoverflow.com/a/19422180/692942, not directly VBScript but the exact same issue (VB is VB across the whole plethora of options, VBA, VB, VB.Net) all use the continuation character to break strings across lines. — user692942, Commented Jun 1, 2016 at 10:23

user692942 · Accepted Answer · 2021-09-14 10:22:09Z

Well I'm going to post an answer anyway, never been a fan of the Fastest Gun in the West Problem

Using the Line Continuation Character

As I mentioned in the comments it sounds like you want to make the SQL command more readable in code. The normal way to do this is using the Line Continuation Character (_) also known as a Statement Break.

Dim sql
sql = "SELECT PATH301 " & _
  "FROM NC301B " & _
  "WHERE EDIPROC like 'P30_' " & _
  "AND (LF301M > 0) " & _
  "AND (PATH301 NOT LIKE '%saptemp%') " & _
  "AND (PATH301 NOT LIKE '%SAPTEMP%') " & _
  "AND (PATH301 NOT LIKE '%usr%') " & _
  "AND (PATH301 NOT LIKE '%Windows%');"

Bear in mind that this differs if we are not dealing with a string, for example to continue an If statement on to two lines would look something like this;

If result = ( _
  condition1 _
  And condition2 _
  And condition3) Then

Because we cannot break a string across lines we cheat by terminating the string continuing the line and then concatenating the string to the next string & _. This also means this will work;

Dim sql
sql = "SELECT PATH301 " _
  & "FROM NC301B " _
  & "WHERE EDIPROC like 'P30_' " _
  & "AND (LF301M > 0) " _
  & "AND (PATH301 NOT LIKE '%saptemp%') " _
  & "AND (PATH301 NOT LIKE '%SAPTEMP%') " _
  & "AND (PATH301 NOT LIKE '%usr%') " _
  & "AND (PATH301 NOT LIKE '%Windows%');"

It's all down to personal preference and neither way has any performance benefit over its counterpart.

Personally, though I find this approach is more trouble than it's worth, especially when it comes to SQL strings.

Using String Concatenation

Say you want to test without one of the conditions let's say

AND (PATH301 NOT LIKE '%SAPTEMP%')

trying to comment out that line will generate a

Microsoft VBScript compilation error:
Syntax error

Here is an example;

Dim sql
sql = "SELECT PATH301 " & _
  "FROM NC301B " & _
  "WHERE EDIPROC like 'P30_' " & _
  "AND (LF301M > 0) " & _
  "AND (PATH301 NOT LIKE '%saptemp%') " & _
  '"AND (PATH301 NOT LIKE '%SAPTEMP%') " & _
  "AND (PATH301 NOT LIKE '%usr%') " & _
  "AND (PATH301 NOT LIKE '%Windows%');"

Which makes it very inflexible, which is why I prefer to use String Concatenation to build up a string, like this;

Dim sql: sql = ""
sql = sql & "SELECT PATH301 "
sql = sql & "FROM NC301B "
sql = sql & "WHERE EDIPROC like 'P30_' "
sql = sql & "AND (LF301M > 0) "
sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') "
sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') "
sql = sql & "AND (PATH301 NOT LIKE '%usr%') "
sql = sql & "AND (PATH301 NOT LIKE '%Windows%');"

Although a little extra work it is far more flexible and allows you to test SQL strings by commenting out lines without affecting the entire SQL string, like so;

Dim sql: sql = ""
sql = sql & "SELECT PATH301 "
sql = sql & "FROM NC301B "
sql = sql & "WHERE EDIPROC like 'P30_' "
sql = sql & "AND (LF301M > 0) "
sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') "
'sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') "
sql = sql & "AND (PATH301 NOT LIKE '%usr%') "
sql = sql & "AND (PATH301 NOT LIKE '%Windows%');"

Another little thing I like to do is follow each line with & vbNewLine like this;

Dim sql: sql = ""
sql = sql & "SELECT PATH301 " & vbNewLine
sql = sql & "FROM NC301B " & vbNewLine
sql = sql & "WHERE EDIPROC like 'P30_' " & vbNewLine
sql = sql & "AND (LF301M > 0) " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%saptemp%') " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%SAPTEMP%') " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%usr%') " & vbNewLine
sql = sql & "AND (PATH301 NOT LIKE '%Windows%');"

That way when outputting the string (be in using Classic ASP, WScript etc) it formats correctly and when displaying in a HTML page (if using Classic ASP) you can easily use

sql = Replace(sql, vbNewLine, "<br />")

to format it correctly, very useful when trying to debug problems with dynamic SQL.

Why not just roll a simple string builder class and each line would call sb.Append("foo"). It would be far faster than calling a string constructor a LOT. That way you can still comment out individual lines. Sample code: learn.microsoft.com/en-us/previous-versions/office/developer/… — Developer Webs, Commented Oct 1, 2019 at 15:40
@DeveloperWebs You certainly could, but I've not seen massive issues with performance when building strings especially ones that are used for SQL statements. Maybe if I was outputting HTML but then I'd just use Response.Write(). — user692942, Commented Oct 1, 2019 at 16:29
With 10 concatenations (you're doing 2 per line plus calling the string constructor multiple times per line) the StringBuilder will be more efficient, and it meets your commenting out condition. Also, if the concatenations are within any type of loop, StringBuilder could be far faster. — Developer Webs, Commented Oct 1, 2019 at 18:14
@DeveloperWebs your preaching to the choir. I never said it wasn’t more efficient but unless you’re doing massive concatenations the performance benefit is going to be minimal. In fact most of the time would use a Stored Procedure instead of hardcoded SQL statements. Let’s also not forget exposing a .Net library via COM has it’s own overheads (registration of COM wrapper etc.). — user692942, Commented Oct 1, 2019 at 18:20

Ansgar Wiechers · Accepted Answer · 2016-06-01 09:40:41Z

The SQL statement is a string, so you can split it into multiple strings and concatenate them. Use the line continuation operator (_) for wrapping the statement. For readability reasons I'd put the query in a variable though, like this:

qry = "SELECT PATH301 FROM NC301B " & _
      "WHERE EDIPROC like 'P30_' AND (LF301M > 0) " & _
      "AND (PATH301 NOT LIKE '%saptemp%') " & _
      "AND (PATH301 NOT LIKE '%SAPTEMP%') " & _
      "AND (PATH301 NOT LIKE '%usr%') " & _
      "AND (PATH301 NOT LIKE '%Windows%');"
rs.open qry, cn, 3

Multiline strings are not supported in VBScript.

Ekkehard.Horner · Accepted Answer · 2016-06-01 09:41:04Z

4

Use line continuation (_) and concatenation or Join:

s = "a b c"
WScript.Echo s
s =   "a " _
    & "b " _
    & "c"
WScript.Echo s
s = Join(Array( _
          "a" _
        , "b" _
        , "c" _
))
WScript.Echo s

output:

cscript 37564704.vbs
a b c
a b c
a b c

answered Jun 1, 2016 at 9:41

Ekkehard.Horner

38.6k2 gold badges48 silver badges98 bronze badges

Add a comment |

Dave · Accepted Answer · 2016-06-01 09:41:12Z

3

Should do what you want, assuming you just want it to look nicer; this doesn't affect any functionality at all...

rs.open "SELECT PATH301 FROM NC301B " & _
"WHERE EDIPROC like 'P30_' " & _
"AND (LF301M > 0) " & _
"AND (PATH301 NOT LIKE '%saptemp%') " & _
"AND (PATH301 NOT LIKE '%SAPTEMP%') " & _ 
"AND (PATH301 NOT LIKE '%usr%')  " & _
"AND (PATH301 NOT LIKE '%Windows%');", cn, 3

answered Jun 1, 2016 at 9:41

Dave

4,3582 gold badges25 silver badges33 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Breaking a String Across Multiple Lines

4 Answers 4

Using the Line Continuation Character

Using String Concatenation

Not the answer you're looking for? Browse other questions tagged
vbscript
or ask your own question.

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Using the Line Continuation Character

Using String Concatenation

Not the answer you're looking for? Browse other questions tagged vbscript or ask your own question.

Linked

Related

Not the answer you're looking for? Browse other questions tagged
vbscript
or ask your own question.