Formating a String in sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Formating a String in sql

Author

Topic

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 11:03:55

Ok so here is an explanation of what i need. I have a table that has emails in it. I need to get those emails to show up as verticle line of names. these will then be sent out via email so i have the following line of code to put them into a string:

select @emails1 = isnull (@emails1 + '; ','' + char(13))+ char(13) + email from emaillist

I have tried putting Char(13) in multiple areas trying to get line break. but the email sends the list in a straight line.

can anyone help me. also if you need more info let me know i will give it to you.

example of desired output
xxxx@dgfasg.com
sfgasg@asgahi.com
saghna@asogh.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-05 : 11:05:41

You need to view your results in Text mode, not in grid mode.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 11:08:12

[code]SELECT email + char(13) as [text()]
FROM emaillist
FOR XML PATH('')[/code]

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 11:12:02

quote:
Originally posted by visakh16

SELECT email + char(13) as [text()]
FROM emaillist
FOR XML PATH('')

where should i insert this code.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-05-05 : 11:14:41

in your query?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 11:16:26

Yes I need to set the @emails1 equal to the values in the table email list.

I then send them out in a email

but i want to them to appear in column. and right now they appear one after another.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 11:18:37

quote:
Originally posted by Potso6767

Yes I need to set the @emails1 equal to the values in the table email list.

I then send them out in a email

but i want to them to appear in column. and right now they appear one after another.

SET @emails1=(SELECT email + char(13) as [text()]
FROM emaillist
FOR XML PATH(''))

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 11:21:09

Nope that one still didnt work

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 11:23:28

thats what comes out if i run the query to text.
Brian.Batdorf@synergit.com#x0D;jacob.ostop@synergit.com#x0D;

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 11:25:30

what should i have @emails1 declared as
right now i have varchar
could that be the problem?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 11:29:32

quote:
Originally posted by Potso6767

what should i have @emails1 declared as
right now i have varchar
could that be the problem?

Can you post your full query used?

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 11:32:30

yes but it is kind of messy right now.. still in the works but here it is

declare @date as datetime
declare @weekago as datetime
set @date= getdate()- 4
select @date
set @weekago = (Select startDate from admin_terms WHERE GETDATE() BETWEEN startDate AND endDate)
select @weekago
set @weekago = @date - 20
SELECT d.classID, d.dateID, d.classDate, p.participantID, n.hoursAttended, u.email
, CASE WHEN n.hoursAttended IS NOT NULL THEN 1
WHEN d.classDate <= GETDATE() THEN 0
ELSE 2
END AS 'status'
, CASE WHEN n.hoursAttended IS NOT NULL THEN CONVERT(varchar(50), n.hoursAttended)
WHEN d.classDate <= GETDATE() THEN 'Data not yet entered'
ELSE 'N/A'
END AS 'hoursDisplay'
INTO #all
FROM classes_dates d
JOIN classes_info i ON d.classID = i.classID AND ISNULL(i.active, 0) = 1
Join sec_users u on i.instructorID=u.userID
JOIN classes_participants p ON d.classID = p.classID AND ISNULL(p.active, 0) = 1
LEFT JOIN participants_progressNotes n ON d.classID = n.classID AND p.participantID = n.participantID AND d.dateID = n.dateID
WHERE d.classDate BETWEEN @weekago AND @date
AND ISNULL(d.active, 0) = 1

SELECT a.classID, a.dateID, a.classDate, a.participantID, a.hoursAttended, a.status, a.hoursDisplay
, pi.FName, pi.LName, u.Email
, ci.team, ci.instructorID, ci.instructorID2, ci.className, ci.startDate, dbo.dayName(ci.dayOfWeek) 'classDay'
, ISNULL(RTRIM(u.LName) + ', ' + RTRIM(u.FName), 'None Assigned') 'instructor', RTRIM(u2.LName) + ', ' + RTRIM(u2.FName) 'instructor2'
into #email
FROM #all a
JOIN classes_info ci ON a.classID = ci.classID
JOIN participants_info pi ON a.participantID = pi.participantID
LEFT JOIN sec_users u ON ci.instructorID = u.userID
LEFT JOIN sec_users u2 ON ci.instructorID2 = u2.userID
WHERE a.status = 0
ORDER BY a.classID, a.classDate, a.participantID
select * from #email
select distinct email
into emaillist from #email
select * from emaillist
declare @tofield varchar(500)
declare @emails varchar(8000)
declare @emails1 varchar(8000)

THIS IS WHERE IM WORKING WITH THE EMAIL LIST

SET @emails1=(SELECT email + char(13) as [text()]
FROM emaillist
FOR XML PATH(''))
select @emails1=isnull (@emails1 + char(13) ,'') + email from emaillist
select @emails1select @emails = isnull (@emails + '; ','') + email from emaillist
select @emails
declare @datestart as varchar(11)
declare @dateend as varchar(11)
set @datestart = @date
set @dateend = @weekago
select @datestart
--send the email out
declare @body1 as varchar (1000)
declare @bodyHTML as varchar (3000)
SELECT @body1 = '<html>' +
'<head>' +
'<title>Attendance</title>' +
'</head>' +
'<body>' +
'<div style="Font-Family: Arial;">' +
'You have attendance that needs to be filled out between the dates of:<br>'

SELECT @bodyHTML = @body1 + @dateend + ' - ' + @datestart + ' ' + @emails1
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'sqlservice',
@recipients=@emails,
@subject = 'Attendance',
@body = @bodyHTML,
@body_format = 'HTML'
SELECT a.classDate, pi.FName, pi.LName, u.Email,
ci.className, ci.startDate, ci.instructorid ,dbo.dayName(ci.dayOfWeek) 'classDay'
, ISNULL(RTRIM(u.LName) + ', ' + RTRIM(u.FName), 'None Assigned') 'instructor', RTRIM(u2.LName) + ', ' + RTRIM(u2.FName) 'instructor2'
into #tabledata
FROM #all a
JOIN classes_info ci ON a.classID = ci.classID
JOIN participants_info pi ON a.participantID = pi.participantID
LEFT JOIN sec_users u ON ci.instructorID = u.userID
LEFT JOIN sec_users u2 ON ci.instructorID2 = u2.userID
order by ci.instructorid
select * from #tabledata

--declare @summaryreport varchar (8000)
--set @summaryreport = isnull (@summaryreport + '; ','') + Fname from #tabledata
drop table #email
drop table #all
drop table emaillist
drop table #tabledata

--end of send
--end of send

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 11:38:35

quote:
Originally posted by Potso6767

yes but it is kind of messy right now.. still in the works but here it is

declare @date as datetime
declare @weekago as datetime
set @date= getdate()- 4
select @date
set @weekago = (Select startDate from admin_terms WHERE GETDATE() BETWEEN startDate AND endDate)
select @weekago
set @weekago = @date - 20
SELECT d.classID, d.dateID, d.classDate, p.participantID, n.hoursAttended, u.email
, CASE WHEN n.hoursAttended IS NOT NULL THEN 1
WHEN d.classDate <= GETDATE() THEN 0
ELSE 2
END AS 'status'
, CASE WHEN n.hoursAttended IS NOT NULL THEN CONVERT(varchar(50), n.hoursAttended)
WHEN d.classDate <= GETDATE() THEN 'Data not yet entered'
ELSE 'N/A'
END AS 'hoursDisplay'
INTO #all
FROM classes_dates d
JOIN classes_info i ON d.classID = i.classID AND ISNULL(i.active, 0) = 1
Join sec_users u on i.instructorID=u.userID
JOIN classes_participants p ON d.classID = p.classID AND ISNULL(p.active, 0) = 1
LEFT JOIN participants_progressNotes n ON d.classID = n.classID AND p.participantID = n.participantID AND d.dateID = n.dateID
WHERE d.classDate BETWEEN @weekago AND @date
AND ISNULL(d.active, 0) = 1

SELECT a.classID, a.dateID, a.classDate, a.participantID, a.hoursAttended, a.status, a.hoursDisplay
, pi.FName, pi.LName, u.Email
, ci.team, ci.instructorID, ci.instructorID2, ci.className, ci.startDate, dbo.dayName(ci.dayOfWeek) 'classDay'
, ISNULL(RTRIM(u.LName) + ', ' + RTRIM(u.FName), 'None Assigned') 'instructor', RTRIM(u2.LName) + ', ' + RTRIM(u2.FName) 'instructor2'
into #email
FROM #all a
JOIN classes_info ci ON a.classID = ci.classID
JOIN participants_info pi ON a.participantID = pi.participantID
LEFT JOIN sec_users u ON ci.instructorID = u.userID
LEFT JOIN sec_users u2 ON ci.instructorID2 = u2.userID
WHERE a.status = 0
ORDER BY a.classID, a.classDate, a.participantID
select * from #email
select distinct email
into emaillist from #email
select * from emaillist
declare @tofield varchar(500)
declare @emails varchar(8000)
declare @emails1 varchar(8000)

THIS IS WHERE IM WORKING WITH THE EMAIL LIST

SET @emails1=(SELECT email + char(13) as [text()]
FROM emaillist
FOR XML PATH(''))
select @emails1=isnull (@emails1 + char(13) ,'') + email from emaillist
select @emails1select @emails = isnull (@emails + '; ','') + email from emaillist
select @emails
declare @datestart as varchar(11)
declare @dateend as varchar(11)
set @datestart = @date
set @dateend = @weekago
select @datestart
--send the email out
declare @body1 as varchar (1000)
declare @bodyHTML as varchar (3000)
SELECT @body1 = '<html>' +
'<head>' +
'<title>Attendance</title>' +
'</head>' +
'<body>' +
'<div style="Font-Family: Arial;">' +
'You have attendance that needs to be filled out between the dates of:<br>'

SELECT @bodyHTML = @body1 + @dateend + ' - ' + @datestart + ' ' + @emails1
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'sqlservice',
@recipients=@emails,
@subject = 'Attendance',
@body = @bodyHTML,
@body_format = 'HTML'
SELECT a.classDate, pi.FName, pi.LName, u.Email,
ci.className, ci.startDate, ci.instructorid ,dbo.dayName(ci.dayOfWeek) 'classDay'
, ISNULL(RTRIM(u.LName) + ', ' + RTRIM(u.FName), 'None Assigned') 'instructor', RTRIM(u2.LName) + ', ' + RTRIM(u2.FName) 'instructor2'
into #tabledata
FROM #all a
JOIN classes_info ci ON a.classID = ci.classID
JOIN participants_info pi ON a.participantID = pi.participantID
LEFT JOIN sec_users u ON ci.instructorID = u.userID
LEFT JOIN sec_users u2 ON ci.instructorID2 = u2.userID
order by ci.instructorid
select * from #tabledata

--declare @summaryreport varchar (8000)
--set @summaryreport = isnull (@summaryreport + '; ','') + Fname from #tabledata
drop table #email
drop table #all
drop table emaillist
drop table #tabledata

--end of send
--end of send

Why are you again concatenating the variable?Can you try running this alone and see if its giving vertical list of emails?

SELECT email + char(13) as [text()]
FROM emaillist
FOR XML PATH('')

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 11:49:58

yes i tried running it without.

Brian.Batdorf@synergit.com#x0D;jacob.ostop@synergit.com#x0D;

thats what i got

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 12:07:41

quote:
Originally posted by Potso6767

yes i tried running it without.

Brian.Batdorf@synergit.com#x0D;jacob.ostop@synergit.com#x0D;

thats what i got

is it still coming in a single line?

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 12:22:18

Yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-05 : 12:28:27

quote:
Originally posted by Potso6767

Yes

Can you select option results as text in query analyser and run this too
SELECT email + char(13) as [data()]
FROM emaillist
FOR XML PATH('')

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 12:34:40

No changes to output

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-05 : 12:48:41

Try this (with Result to Text on)

declare @em varchar(2000)

select @em = coalesce(@em + char(13), '') + email
from emaillist

select @em

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 12:54:41

ok i tried that and i got different results.. they were in a verticle format. but the in the email they dont appear that way

Potso6767
Starting Member

18 Posts

Posted - 2008-05-05 : 12:56:20

My code to send email has the text as html
could that be the problem?

@body_format = 'HTML'

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources