FIX LENGTH

Question

Post reply

FIX LENGTH

Maique

SSC Eights!

Points: 913
More actions
March 1, 2011 at 8:15 am

#246312

I need to create a fix length file for a customer, but I ran into a for you guys a small issue, when I select the first 25 characters of a description if the description is more than 25 characters then is fine but if less then I have the problem.

see sample query.

INSERT INTO Tran_file (id,RAW_data)

SELECT 2 as id,

'US1'+

CONVERT(VARCHAR(8), GETDATE(), 1)+

'1001'+

'1'+

'000'+

CAST(ORDACR# AS VARCHAR (3))+

LEFT(RTRIM(LTRIM(ARMNAME)),25) --Description field

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

to get fixed length, you'll need to convert to CHAR datatypes, so they are padded with spaces.

something like this maybe?:



INSERT INTO Tran_file (id,RAW_data)

SELECT 2 as id,

'US1'+

CONVERT(CHAR(8), GETDATE(), 1)+

'1001'+

'1'+

'000'+

CAST(ORDACR# AS CHAR (3))+

CONVERT(CHAR(25),LEFT(RTRIM(LTRIM(ARMNAME)),25)) --Description field

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!