I've been working on this code all day. I have approximately 13 columns. Each Column has a start position and end position (see below). I created this in a table and defined the position, it's still not working for me. What am I doing wrong?
FiceCode char(6), -- starting position 1, field length 6
StateStudID char(10), -- starting position 7, field length 10
CampusStudID char(10), -- starting position 17, field length 10
LastName char(25), -- starting position 27, field length 25
FirstName char(15), -- starting position 52, field length 15
MiddleIName char(15), -- starting position 67, field length 15
Suffix char(3), -- starting position 82, field length 3
DateofBirth char(8), -- starting position 85, field length 8
Filler1 char(2), -- starting position 93, field length 2
Gender char(1), -- starting position 95, field length 1
Filler2 char(6), -- starting position 96, field length 6
SSN char(9), -- starting position 102, field length 9
FieldNum char(1), -- starting position 111, field length 1
I need a text output file that will define each start position.
I also used:
right(replicate('0',25) + cast(last_name as char(25)), 25) in my sql statement. However, when I add the first_name, I can't get it to start in position 52.
Are you trying to output your table into the format you have specified, or are you trying to import a text file with that specification.
If it is the former, you could probably do something like:
SELECT FiceCode + Space(6-len(FiceCode)) + StateStudID+ Space(10-len(StateStudID)) + etc.
This will output your fields and pad output with spaces depending on character length within the field. You would swap the function to be before the field name if you needed leading spaces instead of trailing spaces.