July 8, 2011 at 3:49 am
Hi,
I have two strings say @Str1 & @Str2. I want to concatenate @Str1 & @Str2 with a new line between them.
@Str1=Sql
@Str2=Server
The output should be
Sql
Server
rather than Sql Server.
How can i do this?
Regards,
Nithin
July 8, 2011 at 4:20 am
Something like
declare @Str1 varchar(32)
declare @Str2 varchar(32)
select @str1='Sql', @Str2='Server'
print @str1 + char(13) + @str2
?
/T
July 8, 2011 at 5:27 am
Hi
When I use this in a select statement as shown i get Sql Server
declare @Str1 varchar(32)
declare @Str2 varchar(32)
select @str1='Sql', @Str2='Server'
select @str1 + char(13) + @str2
Am I doing something wrong???
July 8, 2011 at 5:32 am
Hakuna Matata :):):) (7/8/2011)
HiWhen I use this in a select statement as shown i get Sql Server
declare @Str1 varchar(32)
declare @Str2 varchar(32)
select @str1='Sql', @Str2='Server'
select @str1 + char(13) + @str2
Am I doing something wrong???
If your doing this in SSMS and have results to Grid. Then you will get it all on one row. SSMS removes the linefeeds before presenting. Change results to text mode (CTRL+T) for a different view on the result 😀
/T
July 8, 2011 at 5:34 am
Hakuna Matata :):):) (7/8/2011)
HiWhen I use this in a select statement as shown i get Sql Server
declare @Str1 varchar(32)
declare @Str2 varchar(32)
select @str1='Sql', @Str2='Server'
select @str1 + char(13) + @str2
Am I doing something wrong???
Use a PRINT instead of a select where highlighted - you'll be able to see the newline when you print
Edit: Or of course - you can display results in text as mentioned above :blush:
July 8, 2011 at 5:53 am
I am trying to implement this into the following function :
CREATE FUNCTION [dbo].[fnItinerary](@Itinerary_id int)
RETURNS VARCHAR(max)
AS
BEGIN
-- Declare the return variable here
declare @tblFirstName table(ItineraryInOne varchar(max))
declare @ItineraryArr varchar(max)=''
insert into @tblFirstName
SELECT AirLine+' '+Origin+' '+Destination
from MailSegment
select @ItineraryArr=ItineraryInOne+CHAR(13)+@ItineraryArr
from @tblFirstName
if(len(@ItineraryArr)<>0)
begin
set @ItineraryArr=left(@ItineraryArr,len(@ItineraryArr)-1)
end
return @ItineraryArr
END
The output I want is
AA LHR DXB
EK DXB BOM
AI BOM DEL
rather than
AA LHR DXB , EK DXB BOM , AI BOM DEL
July 8, 2011 at 6:13 am
I must say that i dont see the problem. Since that is the way you will get it out. But as i said. SSMS will NOT display the line breaks in GRID mode. The TEXT mode will. Reports will. Applications will see it this way... how they display it is a different matter.
You cant force an application to display the line feed. Not if its supposed to be one record. Now if we are talking about breaking up the data on multiple rows then thats a totally different question.
One addition:
If its an old application that gets this value then the CHAR(13) might not be enough. Old school new lines are CHAR(13) + CHAR(10).
/T
July 8, 2011 at 6:38 am
Tommy is right. Adding CHAR(13) or even CHAR(13) + CHAR(10) to your output string does NOT create a new row. It just creates a row with control characters in it which may be recognized by an application (SSMS in results-to-text mode) or ignored by an application (SSMS in results-to-grid mode). Result sets in SQL are not simply "lines". Confusing a new line with a new row misses something fundamental about what a database is and does.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 8, 2011 at 7:13 am
I am using the function to send a report using Database Mail as shown below.
Is there any other go wherin I can I can show multiple itinerary's on different lines.
DECLARE @tableHeader NVARCHAR(MAX) ;
DECLARE @tableData NVARCHAR(MAX) ;
DECLARE @tableEnd NVARCHAR(MAX) ;
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHeader =
N'<H4><font face="CALIBRI"> Travel Report </H4>' +
N'<table border 0.5px BORDERCOLOR="Black" width="90%" cellpadding="0" cellspacing="0" style="font-family:calibri; font-size:15" >' +
N'<tr style="color:white; background-color:#CC0000;"><td align="center" width="7%">Reference</td><td align="center" width="35%">Passenger Name</td>
<td align="center" width="40">Itinerary</td></tr>'
SET @tableData = CAST (( SELECT
'center' AS 'td/@align',
td=Reference, '',
'left' AS 'td/@align',
td=Pax , '',
'center' AS 'td/@align',
'left' AS 'td/@align',
td=dbo.fnItinerary(Invoice.Itinerary_Id)
from MailSegment
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )
SET @tableEnd =N'</table>' ;
SET @tableHtml=@tableHeader+@tableData+@tableEnd;
IF @tableData <>''
EXEC msdb.dbo.sp_send_dbmail
@recipients='nithin@mystifly.com',
@profile_name = 'Admin',
@subject = ' Travel Report ',
@body = @tableHTML,
@body_format = 'HTML' ;
ELSE
EXEC msdb.dbo.sp_send_dbmail
@recipients='nithin@mystifly.com',
@profile_name = 'Admin',
@subject = 'Travel Report ',
@body = 'No Data',
@body_format = 'HTML' ;
July 14, 2011 at 1:47 am
Hakuna Matata :):):) (7/8/2011)
I am using the function to send a report using Database Mail as shown below.Is there any other go wherin I can I can show multiple itinerary's on different lines.
DECLARE @tableHeader NVARCHAR(MAX) ;
DECLARE @tableData NVARCHAR(MAX) ;
DECLARE @tableEnd NVARCHAR(MAX) ;
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHeader =
N'<H4><font face="CALIBRI"> Travel Report </H4>' +
N'<table border 0.5px BORDERCOLOR="Black" width="90%" cellpadding="0" cellspacing="0" style="font-family:calibri; font-size:15" >' +
N'<tr style="color:white; background-color:#CC0000;"><td align="center" width="7%">Reference</td><td align="center" width="35%">Passenger Name</td>
<td align="center" width="40">Itinerary</td></tr>'
SET @tableData = CAST (( SELECT
'center' AS 'td/@align',
td=Reference, '',
'left' AS 'td/@align',
td=Pax , '',
'center' AS 'td/@align',
'left' AS 'td/@align',
td=dbo.fnItinerary(Invoice.Itinerary_Id)
from MailSegment
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) )
SET @tableEnd =N'</table>' ;
SET @tableHtml=@tableHeader+@tableData+@tableEnd;
IF @tableData <>''
EXEC msdb.dbo.sp_send_dbmail
@recipients='nithin@mystifly.com',
@profile_name = 'Admin',
@subject = ' Travel Report ',
@body = @tableHTML,
@body_format = 'HTML' ;
ELSE
EXEC msdb.dbo.sp_send_dbmail
@recipients='nithin@mystifly.com',
@profile_name = 'Admin',
@subject = 'Travel Report ',
@body = 'No Data',
@body_format = 'HTML' ;
Ah HTML. You know that does change ALOT. Char(13) doesnt work in HTML. Replace it with <br>
/T
July 14, 2011 at 5:57 am
I tried implementing by replacing with br, but br gets appended to the string.
July 14, 2011 at 7:02 am
Hakuna Matata :):):) (7/14/2011)
I tried implementing by replacing with br, but br gets appended to the string.
Well you cant replace it with just BR you need the whole part "<br>" (without the "). Now the problem is that for XML path seams to mess up that (it replaces it with the codes for it instead. & lt; and & gt; (without the space after the & sign).
So plan B. Keep the char(13) inside the function and add a replace to the SET command like:
SET @tableData = replace(CAST (( SELECT
'center' AS 'td/@align1',
td=Reference, '',
'left' AS 'td/@align2',
td=Pax , '',
'center' AS 'td/@align3',
'left' AS 'td/@align4',
td=dbo.fnItinerary(0)
from MailSegment
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX)), '& #x0D;', '<br>')
REMOVE THE SPACE BETWEEN the % and the # (cant get these frigging forums to display it right)
The HTML output should now have <br> in it instead of the char(13). Looks okayish from my view anyway.
/T
July 14, 2011 at 7:16 am
OMG:-):-):-) It works like a charm. Thaaaaaaaaaaaaaaaaaank Yoooooooooooooouuuuuu!!!!!!!! :-):-):-)
July 14, 2011 at 8:01 am
Your welcome 😎
/T
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply