Retaining format of SQL statement when stored in database

Question

We store several sql statements in one of our tables. For example, the following SQL statement will be stored in [Validations].[SqlSource]:

select 
VendorId,
VendorName,
VendorAddress,
VendorCity,
VendorState,
VendorZip
from Vendor

The problem is that when we copy this SQL directly from the table, it'll lose the carriage returns. This means that pasting the sql will look like this:

select VendorId, VendorName, VendorAddress, VendorCity, VendorState, VendorZip from Vendor

It's not an issue with this sql since it's short, but when the SQL is long, then the string when pasting will be long.

Is there a way to keep the formatting when I copy the string and paste to a text editor?

I added an example of how it's inserted into the table:

create table toerase_SQL
(
    SqlSource varchar(max)
)
insert into toerase_SQL 
select   
'select '+
'VendorId, '+
'VendorName, '+
'VendorAddress, '+
'VendorCity, '+
'VendorState, '+
'VendorZip '+
'from Vendor '

select * from toerase_SQL

What's the type of `SqlSource`? What tool are you using to store and retrieve the data? — The Impaler, Apr 24 '23 at 20:07
SQL will preserve line breaks. Most likely the issue you're seeing is down to the tool you're using to view/copy the value. From your tags it looks like you're using SSMS; see https://stackoverflow.com/a/37284582/361842 — JohnLBevan, Apr 24 '23 at 20:07
Have you tried putting a line-break in your inserts to see if it's preserved? e.g. https://dbfiddle.uk/Fqi3QtZ1 — EdmCoff, Apr 24 '23 at 20:51
@EdmCoff: I haven't tested it, but I'm pretty sure it'll work. The problem is that it would involve another step, aside from adding the single quotes. — fdkgfosfskjdlsjdlkfsf, Apr 24 '23 at 20:55
You don't have to write out the CRLF explicitly. As a previous comment shows (and my second example shows) you can just not close your string so that the line-breaks are actually part of it — EdmCoff, Apr 24 '23 at 21:08
how do you copy it? it seems the problem is related to usage of SSMS or similar clients — siggemannen, Apr 25 '23 at 10:21

score 3 · Answer 1 · answered Apr 24 '23 at 21:08

When you concatenate strings with something like

SELECT
'a' +
'b' +
'c'

it means, take the string 'a' and append the string 'b' and then append the string 'c', so the result is 'abc'. It does not automatically add line-breaks (nor spaces) that happen to be near the plus/concatenation operator.

It is the same as

SELECT
'a'+'b'+'c'

You will need to include the line-breaks in the string in order to select them.

For example,

SELECT
'a
b
c'

has the line-breaks captured within the string itself.

Or you could add line-breaks explicitly, like

SELECT
'a' + CHAR(13)+CHAR(10)+
'b' + CHAR(13)+CHAR(10)+
'c'

You can see both of these methods working in this Fiddle.

Retaining format of SQL statement when stored in database

1 Answers1