February 3, 2019 at 5:07 am
I am trying to develop a program that prints greetings card verses which contain long strings of text.
I want to be able to format the text, so that the line breaks are in the right place in the text. It would not be possible when the query is run so it must be done on the stored data.
Is this possible?
February 3, 2019 at 5:24 am
yes, i store queries like that in a table, and execute them all the time.
there are two simple ways, note i had to escape the single quotes within the commands:
the second example is typical when you are building commands from metadata, where you substitute values from within a query.
first example:
DECLARE @cmd VARCHAR(MAX) =
'
SELECT top 100
mail.send_request_date As SentDate,
sent_status As Reason,
err.[description],
mail.*
FROM [msdb].[dbo].[sysmail_allitems] mail
inner join [msdb].[dbo].[sysmail_event_log] err
ON err.mailitem_id = mail.mailitem_id
WHERE mail.sent_status <> ''SENT''
order by mailitem_id desc
'
second example:
DECLARE @cmd VARCHAR(MAX);
SELECT @cmd='SELECT top 100' + CHAR(13) + CHAR(10)
+ 'mail.send_request_date As SentDate,' + CHAR(13) + CHAR(10)
+ 'sent_status As Reason,' + CHAR(13) + CHAR(10)
+ ' err.[description],' + CHAR(13) + CHAR(10)
+ ' mail.*' + CHAR(13) + CHAR(10)
+ 'FROM [msdb].[dbo].[sysmail_allitems] mail ' + CHAR(13) + CHAR(10)
+ ' inner join [msdb].[dbo].[sysmail_event_log] err' + CHAR(13) + CHAR(10)
+ ' ON err.mailitem_id = mail.mailitem_id' + CHAR(13) + CHAR(10)
+ 'WHERE mail.sent_status <> ''SENT''' + CHAR(13) + CHAR(10)
+ ' order by mailitem_id desc' + CHAR(13) + CHAR(10)
Lowell
February 3, 2019 at 5:27 am
All the text types (char, varchar, nchar, varchar, text and ntext (although don't use the last 2)) can store the characters for Line Breaks and Carriage Returns (ASCII Code 10 and 13 respectively).
By default, when you copy and paste the data from SSMS those characters will be removed: there is a setting you need to switch on, which you can find here: Tools -> Options -> Query Results -> SQL Server -> Results to Grid -> Retain CR/LF on copy or Save. Otherwise, if you aren't getting line breaks, but have inserted them, this means that you are eliminating them somewhere; either in your SQL or in your application (for example, in HTML, a line break in text alone doesn't display one, you need to use <br />).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 3, 2019 at 5:56 am
Do you add the LF [CHAR(10)] to the data thats aready stored or do you add the LF as you are uploading the data?
Here is a typical verse:
I bet youre so excited Excited fit to burst Not every day is a birthday And this one your very first.
The line feed needs to be where the capital letter is.
I am not very experienced so may need more help!
February 3, 2019 at 6:34 am
you want to store it with the line feeds in place already.
INSERT INTO YourTable ( ID, NickName,Category,Phrase)
SELECT 1,'excitedBirthday','Birthday',
'I bet you''re so excited
Excited fit to burst
Not every day is a birthday
And this one your very first'
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply