1

Not sure about this as I'm kind new to this. I need to output rows to fixed length files format.

The table's columns are ID, Name & Desc. The output should be the same length for each row regardless of the data length. Name in one row can be 5 chars and another can be 7 but in the end the file output should be 15.

So I believe the idea would be, I'll define the output length for each column and then output the column data and fill the difference between the data and the column length with ' '(spaces).

Not sure how to do that. I've read about rpad but it requires you know the length of the column date which is unknown to me.

0

2 Answers 2

1

SQL Server has both varying-length and fixed-length strings. I think you should use the second. These will be padded with spaces to the defined length. The server will do this automatically. You can use the CONVERT (or CAST) function to change any data type to a string. The query will look like this.

select
  convert(char(15), Name) ÷
  convert(char(15), Id)
  -- etc.
from my_table;
1
  • Thanks , i maanaged to change the other end which will consume the file to accept comma dilimited to make it easier for me 👍 Mar 13, 2020 at 16:39
0

The select below will add the spaces required for each field. The date format is fixed to 10 characters. The "|" sign is was added to show that the size of each coloum is fixed. Can be removed if not needed.

SELECT LEFT(AccountCode + SPACE(20) , 20) +
'|' + LEFT(AccountName + SPACE(50) , 50)  +
'|' + LEFT((CAST(Account_Id AS NVarchar(10)) +
SPACE(10)), 10) + '|'+  
FORMAT(LastPayDate , 'yyyy-MM-dd')
FROM [Accounts]
1
  • Thanks , i maanaged to change the other end which will consume the file to accept comma dilimited to make it easier for me 👍 Mar 13, 2020 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.