Tab Delimited or Fixed Width Columns

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

General SQL Server Forums

New to SQL Server Programming

Tab Delimited or Fixed Width Columns

Author

Topic

jpost
Starting Member

43 Posts

Posted - 2012-09-13 : 14:04:43

When I run the query below and export the results by right clicking and saving the results as a tab delimited text file it seems that any field that uses returns NULL as a value, max spaces the field. This in turn pushes all my data down and it won't line up when trying to import into another program. So is there a way to stop this from happening or setting each column field to a fixed with? Here is my script:

select [Student Number], IRN, Name, Gender, Address, city, state, zip, phone, birthdate, Grade From
(select distinct
s.studentNumber as'Student Number',
case sc.number
when 5101 then '029876'
when 5102 then '145086'
when 5111 then '112581'
when 5112 then '145078'
when 5121 then '138966'
when 5122 then '138958'
when 5123 then '011369'
when 5131 then '086579'
when 5135 then '142638'
when 5132 then '029868'
when 5133 then '065433'
when 5137 then '011368'
when 5136 then '011370'
when 5134 then '123307'
end as 'IRN',
s.lastName + ', ' + s.firstName as 'Name',
s.gender as 'Gender',
a.number + ' ' + a.street + ' ' + a.tag as 'Address',
a.city,
a.state,
a.zip,
hh.phone as 'Phone',
convert(varchar,s.birthdate,111)AS 'birthDate',
s.grade as 'Grade',
se.teacherdisplay as 'Homeroom',
ROW_NUMBER() OVER (PARTITION BY s.studentnumber ORDER BY s.studentnumber) AS RN
From student s
INNER JOIN school sc on sc.schoolID = s.schoolID
INNER JOIN HouseholdMember hm ON hm.personID = s.personID
INNER JOIN Household hh ON hh.householdID = hm.householdID
INNER JOIN HouseholdLocation hl ON hl.householdID = hh.householdID
INNER JOIN Address a ON a.addressID = hl.addressID
JOIN Roster r ON r.personID = s.personID
JOIN Section se ON se.sectionID = r.sectionID
JOIN SectionPlacement sp ON sp.sectionID = se.sectionID
JOIN Period p ON p.periodID = sp.periodID
JOIN Course c ON c.courseID = se.courseID
JOIN Calendar ca ON ca.calendarID = c.calendarID AND ca.calendarID = s.calendarID and sc.schoolID = ca.schoolID
JOIN SchoolYear sy ON sy.endYear = ca.endYear
where s.calendarID in (8,12,16,20,24,28,32,36,40,44,48,52,56,60) and s.startYear = '2012'
and ((( p.seq ='2') or (c.homeroom = '1'))))s
where RN = 1

I tried using RIGHT('000000' + s.studentNumber, 6) as 'Student Number to make my student number 6 characters not matter what and also did this on other fields but this didn't seem to work either.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 14:27:11

whats the actual datatype of those fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jpost
Starting Member

43 Posts

Posted - 2012-09-13 : 14:51:13

What would be the easiest way for me to find that out?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 14:56:16

go to table and check it

or query DATA_TYPE column in INFORMATION_SCHEMA.COLUMNS catalog view

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jpost
Starting Member

43 Posts

Posted - 2012-09-13 : 15:26:12

s.studentNumber is varchar (15) null
sc.number is varchar (7) not null
s.lastName varchar 40, not null + ', ' + s.firstName varchar 35, null
s.gender is char 1, null
a.number is varchar 12, null + ' ' + a.street is varchar 30, null + ' ' + a.tag is varchar 20, null
a.city is varchar 24, null
a.state varchar 2, null
a.zip is varchar 10, null
hh.phone is varchar 25, null
convert(varchar,s.birthdate,111
s.grade is varchar 4, null
se.teacherdisplay is varchar 30, null

jpost
Starting Member

43 Posts

Posted - 2012-09-17 : 08:22:59

Is the above info what you were looking for?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-17 : 10:46:39

quote:
Originally posted by jpost

Is the above info what you were looking for?

yep but i dont think there's a problem in way its currently though as varchar will never cause it to assume full length

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jpost
Starting Member

43 Posts

Posted - 2012-09-17 : 13:18:44

I found a way around it, I just used
left(isnull(s.studentNumber,'') + space(9),9) as 'Student Number'
for every field and it did what I wanted. Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources