Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    2

    Unanswered: filling cell with zeros..changing justification how???

    I am prepping a file to be imported into our Lawson system.


    The quantity field is 9 characters and needs to be filled with zeros..so if the number is 32, the number would be 000000032. Any idea how to do this in SQL Server? In access you can use the format command and just type in the 000000000 and your in.

    Also, any idea how to force left and right justification. Here I need to specify a certain amount of spaces and in one field force left and in another force right. Again, Access has easy formating commands but i don't see them for SL Server.

    Yea .. I am a horrible newbie. Sheez.

    Thanks in advance to anyone who can help!

    Regards

    M

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What version of SQL Server are you using? If 7.0 or 2K I would suggest using DTS. DTS allows you to modify data on the fly. If you can't use DTS then I would load the data into a holding table, make changes and then update your Lawson system tables.

    Post back with what you are conforatable with and I or someone will help.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2003
    Posts
    2

    Version and question MS SQL 2000

    Thanks. It is MS SQL 2000. I am working in DTS. I see where I can design the table, I know how I can manually identify the data types...but I am not seeing in MS SQL 2000 DTS where I can

    1) force left justify the data in the cells
    2) add zeros to fill to a specified number of spaces (9)
    i.e. 32 would be 000000032

    In Access there are commands like
    ! = force left alignment

    000000000 space holder for a nine digit space
    which would give the 000000032 when applied

    I believe MSSQL Server auto justifies to right

    Also, I believe that char(5) for instance, would output whatever characters are present, plus spaces to make up the difference
    For instance a value of 189 would be ' 189' (two spaces and the three digits).


    The reason is that Lawson requires a 358 record length..no commas, the fields are determined by fixed spacing..which sometimes must have zeros and other times must use spaces.

    By the way..anyone using Lawson Forecasting IC519 Import?

    Thanks

    Merlin

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Version and question MS SQL 2000

    I have to do something similar with another application. Try this.

    Assume:
    Code:
    CREATE TABLE MyTable (
        MyValue Int NOT NULL
    )
    
    INSERT INTO MyTable VALUES (1)
    INSERT INTO MyTable VALUES (123)
    INSERT INTO MyTable VALUES (1234)
    INSERT INTO MyTable VALUES (12345)
    INSERT INTO MyTable VALUES (123456)
    Then use:
    Code:
    SELECT
      Right('000000000' + Cast(MyValue as Varchar(10)), 9) as MyValue
    FROM
      MyTable
    Originally posted by Merlin
    Thanks. It is MS SQL 2000. I am working in DTS. I see where I can design the table, I know how I can manually identify the data types...but I am not seeing in MS SQL 2000 DTS where I can

    1) force left justify the data in the cells
    2) add zeros to fill to a specified number of spaces (9)
    i.e. 32 would be 000000032

    In Access there are commands like
    ! = force left alignment

    000000000 space holder for a nine digit space
    which would give the 000000032 when applied

    I believe MSSQL Server auto justifies to right

    Also, I believe that char(5) for instance, would output whatever characters are present, plus spaces to make up the difference
    For instance a value of 189 would be ' 189' (two spaces and the three digits).


    The reason is that Lawson requires a 358 record length..no commas, the fields are determined by fixed spacing..which sometimes must have zeros and other times must use spaces.

    By the way..anyone using Lawson Forecasting IC519 Import?

    Thanks

    Merlin

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Merlin
    I have seen people do this before. If I remember right, it is something like

    right ('000000000'+convert(varchar(10), value), 9)

    It looks ugly, but then, Access looks much uglier to me ;-)
    Hope this helps
    ~Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •