SQL Server Performance

Convert

Discussion in 'SQL Server 2005 General DBA Questions' started by trami, Aug 4, 2008.

  1. trami New Member

    I am working on a conversion project and need to change a few fields. The incoming data on my file for amount_due is numeric(7,0), so the field looks lik 5995 or 16792. I need to change the field to appear as follows: 59.95 and 167.92. Can anyone tell me how I would accomplish this?
  2. MichaelB Member

    Trami,
    Welcome to the SSP forums!
    I think this will work for you!
    create table #test (numbers numeric(7,0))
    insert into #test
    select 5995
    insert into #test
    select 16792

    select cast(cast(left(numbers,len(numbers)-2) as varchar(10)) + '.' + cast(right(numbers,2) as char(2)) as numeric(7,2)) from #test


    drop table #test

  3. trami New Member

    Thanks, I am glad to be a part of the forum. Your solution worked, thanks for the response.
  4. preethi Member

    Try SELECT (Amount_Due/100.0) AS Amount in the query
    BTW, Welcome to the forum
  5. Madhivanan Moderator

    orselect
    cast(Amount_Due/100.0 as decimal(12,2)) from your_table
  6. trami New Member

    Thanks, I am glad to be a part of the forum. I appreciate your help, the solution worked.
  7. trami New Member

    Evidently the circulation system we are using does not like the decimal included in the amountdue field. I have done a few tests and determined that the field should appear as follows:
    5995 - needs to be 000000000000000059950000
    and
    16792 - needs to be 000000000000000167920000
    In other words, the field will always have 4 zeroes on the right and an unknown number of zeroes on the left depending on the actual number characters already in the field. The total converted field length should be 24.
    Can someone point me in the right direction on this?
    Thanks
  8. Adriaan New Member

    Perhaps something like this ...
    declare @v varchar(30)
    set @v = '2873'
    select replicate('0', (21 - LEN(@v))) + @v + '0000'
    -------------------------------
    0000000000000000028730000
    I'm using a string variable here. If you have a numeric column, you would need to use CAST to turn it into a string:

    select replicate('0', (21 - LEN(CAST([num_column] AS VARCHAR(21))))) + CAST([num_column] AS VARCHAR(21)) + '0000'
    from my_table
    In case you ever run into numeric values that require more than 21 characters to print, you would run into problems.
  9. Madhivanan Moderator

    [quote user="Adriaan"]
    Perhaps something like this ...
    declare @v varchar(30)
    set @v = '2873'
    select replicate('0', (21 - LEN(@v))) + @v + '0000'
    -------------------------------
    0000000000000000028730000
    I'm using a string variable here. If you have a numeric column, you would need to use CAST to turn it into a string:

    select replicate('0', (21 - LEN(CAST([num_column] AS VARCHAR(21))))) + CAST([num_column] AS VARCHAR(21)) + '0000'
    from my_table
    In case you ever run into numeric values that require more than 21 characters to print, you would run into problems.
    [/quote]
    If total length of the column is 24, in your code 21 should be replaced with 20
  10. Adriaan New Member

    Thanks Mahivanan - I managed to overlook that detail, and was just guessing the length.
  11. trami New Member

    Ok, me again. Sorry for being a pest. I now have to determine the remaining liability on eash subscription. I have completed this step; however the data is appearing as follows:
    20.99 - but it needs to be length 24 and appear like 000000000000000020990000
    32.33 - but it needs to be length 24 and appear like 000000000000000032330000
    Once again I am not sure how to accomplish this. I am sure with some time I could figure it out but I am under the gun a bit to get this project done.
    Thanks for all the help I have received.
  12. Madhivanan Moderator

    Using Adriaan's approachdeclare
    @v varchar(30)set
    @v = '20.99'set
    @v=replace(@v,'.','')select
    replicate('0', (20 - LEN(@v))) + @v + '0000'
  13. Adriaan New Member

    Are you sure you still need 4 trailing zeroes when there are two decimals? I would expect the trailing zeroes are for decimals.
  14. trami New Member

    The amoutdue field in our circulation system is decimal(24,6) so I will still need the 4 trailing zeroes.
  15. Adriaan New Member

    You're right - I forgot you already had two decimals in there.
  16. Madhivanan Moderator

    [quote user="trami"]
    The amoutdue field in our circulation system is decimal(24,6) so I will still need the 4 trailing zeroes.
    [/quote]
    Did you try my method?

Share This Page