Inserting TAB between Fields "Strung" together

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)

Inserting TAB between Fields "Strung" together

Author

Topic

JacobPressures
Posting Yak Master

112 Posts

Posted - 2009-10-01 : 10:06:50

I have to use TABs to delimit my columns. Currently, I can partially do a comma delimited string, but not completely because of datatype conversion issues.

I really don't understand the COALESCE function. I read a little on it. I stole it from another script we have in the office. Where you see commas i need TABs. I've tried different things but i don't know how to add them. I really need a good review on adding characters like this and converting from one datatype to another.

DataType of Fields:
StudentID int
CourseNumber varchar
SectionNumber varchar
StaffNumber varchar
SchoolNumber varchar


SELECT RTRIM('"'+
COALESCE(s.StudentID, '') +'","'+ 
s.LastName +'","'+  
s.FirstName + ' ' + s.MiddleName +'","'+   
sa.GradeLevelName+'","'+
c.CourseNumber +'","'+ 
c.FullName+'","'+
c.SectionNumber +'","'+
COALESCE((SELECT TOP(1) t.[Name]
		  FROM ScheduledSection sks  
		  JOIN TimeSlot t ON t.TimeSlotID = sks.TimeSlotID 
		  WHERE c.CourseID = sks.SectionID
		  ORDER BY t.Sequence), '') +'","'+
sf.StaffNumber +'","'+ 
sf.LastName +'","'+ 
sf.FirstName +'","'+
sch.SchoolNumber +'","'+	
'' +'"')
FROM dbo.StudentSchedule ss
LEFT JOIN dbo.Student s ON s.StudentID = ss.StudentID
JOIN dbo.StudentAcadSession sa ON s.StudentID = sa.StudentID
JOIN Course c ON c.CourseID = ss.SectionID
LEFT JOIN Staff sf ON c.PrimaryTeacherID = sf.StaffID
JOIN dbo.AcadSession a ON a.AcadSessionID = sa.AcadSessionID
JOIN dbo.School sch ON sch.SchoolID = a.SchoolID

Thanks very much for your helps guys!

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 10:35:42

JacobPressures,

To capture and use a tab:

DECLARE @tab CHAR(2)
SET @tab = CHAR(9)

Then place as many tabs into your string as you need by concatenation of the @tab variable.

Example:
PRINT 'First String + @tab + @tab + 'Last String'

Or use the first method in combination with the REPLICATE function in T-SQL

Example: (This statement will place 15 tabs between the string)

DECLARE @tab CHAR(2)
SET @tab = CHAR(9)
SELECT 'Hello' + REPLICATE(@tab,15) + 'World' AS Example

Hope this helps you out!

JacobPressures
Posting Yak Master

112 Posts

Posted - 2009-10-01 : 11:00:40

Ok, I looked up char function verses the ascii function and found a useful article. I was using ascii instead of char. So that is where i was going wrong. Thanks for the help!

I'm still lost on the coalesce function. I did read that it is not good to use it for string concatenation. I read that using the + operator may not be a good idea either.

I found this article: "Coalesce is not the answer to string concatentation in T-SQL" at http://msmvps.com/blogs/robfarley/archive/2007/04/08/coalesce-is-not-the-answer-to-string-concatentation-in-t-sql.aspx.

mivey4
Yak Posting Veteran

66 Posts

Posted - 2009-10-01 : 11:09:38

Your linked article is accurate and correct. You shouldn't use the COALESCE function to concatenate strings, though many folks do it anyway. I am not a big fan of that method though.

You should however consider using the REPLICATE function that I referenced in the previous post for what you're trying to acheive.

I don't know where you received your information discouraging the use of the + operator for the intent of string concatenation. That is totally incorrect. The operator serves a dual purpose in T-SQL as either an operator of math or a string concatenator depending on the context for which it is used.

The only way it shouldn't be used as a concatenator is if the user doesn't understand how to implement it.

JacobPressures
Posting Yak Master

112 Posts

Posted - 2009-10-01 : 15:42:30

Thanks guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources