Post reply

How to Stuff the positions with blanks?

  • August 29, 2018 at 3:22 pm

    #367697

    Hi ...
    I got a format to fill the positions to create a long string.

    Pos. Name Attribute
    1-3 Transaction 123
    4-6 Number 567
    7-12 Loan Number *
    13-41 Filler  
    42-48 Interest  LZF, +/-
    49-89 Filler2  
    90-102 Expanded acct Number '000' & accountnumber with leading zeros

    So,
    I am having something like this 
    SELECT '123567*' + Space(41-8)+ cast(ltrim(rtrim(Interest)) as varchar (7))+  Space(89-49) + '000'+ '0000123456' as String

    Issue here is interest is not 7 characters every time , it can be 4 or 5 . If it is 5 characters how to fill the rest 2 characters ?
    Example of Interest :
    345.6
    1234.56
    890.34

  • August 29, 2018 at 3:30 pm

    #2003916

    SELECT '123567*' + Space(41-8)+ left(cast(ltrim(rtrim(Interest)) as varchar (7)) + Space(6), 7)+ Space(89-49) + '000'+ '0000123456' as String

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • August 29, 2018 at 3:40 pm

    #2003918

    Casting things to CHAR will force a specific length, so something a bit like:
    Select
     Cast(trans As Char(3))
     +Cast(Number As Char(3))
     +Cast(Loan As Char(6))
     +Space(41-13+1)
     +Cast(Interest As Char(7))
     +Space(89-49+1)
     +Right(Replicate('0', 100) + Cast(Acc As VarChar(10)), 102-90+1)
    From (Values
    (123,567,'*',345.6,34565),
    (123,567,'*',1234.56,34565),
    (123,567,'*',890.34,34565)
    ) D(Trans, Number, Loan, Interest, Acc)

Viewing 3 posts - 1 through 2 (of 2 total)

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