Post reply

FIX LENGTH

  • 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

  • March 1, 2011 at 8:21 am

    #1292970

    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!

  • March 1, 2011 at 8:24 am

    #1292973

    Instead of using LEFT(RTRIM(LTRIM(ARMNAME)),25)

    use

    LEFT(RTRIM(LTRIM(ARMNAME))+SPACE(25),25)

    You'll end up with 25 characters everytime.

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

  • March 1, 2011 at 8:26 am

    #1292976

    Thank you - thank you

  • March 1, 2011 at 8:27 am

    #1292977

    --Removed--- 🙂

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

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

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