SQL Server – Does T-SQL Support Line-Continuation for Long Strings?

scriptingsql serverssmst-sql

I sometimes have a SQL script that has one or more super-long (sometimes even stupid-long) strings. Typically these are VARBINARY literals / constants that represent files / Assemblies, but occasionally they are text.

The primary problem with really long strings is that some text editors do not handle them all that well. For example, I have a VARBINARY literal that I use in a CREATE ASSEMBLY [AssemblyName] FROM 0x.... statement, and the Assembly itself is just over 1 MB in size, which equates to just over 2 million characters in a text file since each byte requires two characters to be represented in hex notation (e.g. 0x1F = a 1 and an F). SQL Server Management Studio (SSMS) does not handle this well and hangs for several seconds as I try to scroll over that line. And in fact, some versions (not sure if this still happens) will even display a warning about long lines when opening a script that has at least one line over a certain length.

A secondary issue is that it complicates the formatting when either using in an editor without word-wrap enabled, or posting online. The problem here is that the slider for the horizontal scroll bar is very narrow and moving it even just a little bit usually scrolls the non-super-long text out of view.

Now, T-SQL doesn't terminate commands with newlines or even semi-colons (though semi-colons are preferred / recommended, starting with SQL Server 2005). So since SQL Server knows how to parse each statement such that it knows when it ends, it seems like splitting the long line across multiple lines, separated only by a newline / carriage-return + line-feed, doesn't seem unreasonable. But this doesn't work in either case.

PRINT 'Line1
Line2';

returns (in the "Messages" tab):

Line1
Line2

And that makes sense enough as the newline is within a literal / constant. But doing this for a VARBINARY also doesn't work.

PRINT 0x1234
5678;

gives me an error.

Best Answer

Thankfully there is support for line-continuation in T-SQL via the \ (backslash) character. Just place that at the end of of a line, right before the newline / carriage-return + line-feed, and the newline will be ignored.

For text strings, this behaves as follows:

PRINT 'Line1\
Line2';

returns (in the "Messages" tab):

Line1Line2

For binary / hex strings, this behaves as follows:

PRINT 0x1234\
5678;

returns (in the "Messages" tab):

0x12345678;

In order to format binary files (Assemblies, Certificates) into text hex-byte strings for use in SQL scripts, I wrote a command-line utility called BinaryFormatter that I have released as open-source on GitHub. Not only does it convert binary file into a text representation, but it also uses line-continuation to spread long VARBINARY literals across as many rows are needed, based on the specified number of character to use per each row. The result is something along the lines of:

4D5A09DE34F178313345A4\
00007F4E39782EFC48D842\
00000000

which I then copy and paste into my script, as shown in the {...} area below:

CREATE ASSEMBLY [AssemblyName]
FROM 0x\
{output from BinaryFormatter}
;

For additional details on this topic, please see my blog post: Line-Continuation in T-SQL

Related Solutions

Alternatives to concatenating strings or going procedural to prevent SQL query code repetition

You are too modest - your SQL is well and concisely written given the task you are undertaking. A few pointers:

t1.name <> t2.name is always true if t1.name = REPLACE(t2.name, 'DUP_', '') - you can drop the former
usually you want union all. union means union all then drop duplicates. It might make no difference in this case but always using union all is a good habit unless you explicitly want to drop any duplicates.

if you are willing for the numeric comparisons to happen after casting to varchar, the following might be worth considering:

create view test_attribs_cast as 
select id, name, attr1, attr2, cast(attr3 as varchar(2000)) as attr3, 
       cast(attr4 as varchar(2000)) as attr4, attr5
from test_attribs;

create view test_attribs_unpivot as 
select id, name, 1 as attr#, attr1 as attr from test_attribs_cast union all
select id, name, 2, attr2 from test_attribs_cast union all
select id, name, 3, attr3 from test_attribs_cast union all
select id, name, 4, attr4 from test_attribs_cast union all
select id, name, 5, attr5 from test_attribs_cast;

select 'attr'||t1.attr# as different, t1.id as id_1, t2.id as id_2, t1.name, 
       t2.name as name_dup, t1.attr as val1, t2.attr as val2
from test_attribs_unpivot t1 join test_attribs_unpivot t2 on(
       t1.id<>t2.id and 
       t1.name = replace(t2.name, 'DUP_', '') and 
       t1.attr#=t2.attr# )
where t1.attr<>t2.attr or (t1.attr is null and t2.attr is not null)
      or (t1.attr is not null and t2.attr is null);

the second view is a kind of unpivot operation - if you are on at least 11g you can do this more concisely with the unpivotclause - see here for an example

I'm say don't go down the procedural route if you can do it in SQL, but...
Dynamic SQL is probably worth considering despite the problems you mention with testing and maintenance

--EDIT--

To answer the more general side of the question, there are techniques to reduce repetition in SQL, including:

Views - you know about that one :)
Common Table Expressions (see here for example)
individual features of the database such as decode (see Leigh's answer for how this can reduce repetition), window functions and hierarchical/recursive queries to name but a few

But you can't bring OO ideas into the SQL world directly - in many cases repetition is fine if the query is readable and well-written, and it would be unwise to resort to dynamic SQL (for example) just to avoid repetition.

The final query including Leigh's suggested change and a CTE instead of a view could look something like this:

with t as ( select id, name, attr#, 
                   decode(attr#,1,attr1,2,attr2,3,attr3,4,attr4,attr5) attr
            from test_attribs
                 cross join (select rownum attr# from dual connect by rownum<=5))
select 'attr'||t1.attr# as different, t1.id as id_1, t2.id as id_2, t1.name, 
       t2.name as name_dup, t1.attr as val1, t2.attr as val2
from t t1 join test_attribs_unpivot t2 
               on( t1.id<>t2.id and 
                   t1.name = replace(t2.name, 'DUP_', '') and 
                   t1.attr#=t2.attr# )
where t1.attr<>t2.attr or (t1.attr is null and t2.attr is not null)
      or (t1.attr is not null and t2.attr is null);

Sql-server – Import CSV File

From what you are describing it sounds like you needs to provide the BULK INSERT command a format file so it knows how to parse your data. You can use the bcp command to auto-generate one for you, however I have always had to edit them afterwards before they work properly. (Lots of testing I am afraid).

The links below should be good starting points for you:

Format Files: http://msdn.microsoft.com/en-us/library/ms190393.aspx

BCP: http://msdn.microsoft.com/en-us/library/ms162802.aspx

BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx

EXAMPLE: http://msdn.microsoft.com/en-us/library/ms178129.aspx

I hope this helps you.

EDIT

Based on the extra information that you have added above it looks like you have set all field terminators to pipes, which is not what you want. You will need to edit the format file on a per column basis.

For instance, in the example above your first two columns are delimited by a semi-colon. The third would need to be delimited by ";|". Here is an example of what I mean for the first few columns:

9.0 
18 
1       SQLCHAR       0       510     ";"     1     web1numdoss 
2       SQLCHAR       0       510     ";"     2     web1dem 
3       SQLCHAR       0       510     ";|"     3     web1def 
...
18      SQLCHAR       0       510     "\r\n"   18    Col018

Best Answer

Related Solutions

Alternatives to concatenating strings or going procedural to prevent SQL query code repetition

Sql-server – Import CSV File

Related Question