Post reply

Need to display decimal value converting it to non decimal 13 character numeric string replacing unused character positions with ''0''

  • November 8, 2006 at 12:27 pm

    #115259

    I need to display decimal value converting it to non decimal 13 characters long numeric string replacing unused character positions with '0'

    Suppose value is 740002.64 then it needs to be displayed as 0000074000264. If the value is 150.00 it needs to be displayed as 0000000015000.

     

    Thanks

  • November 8, 2006 at 12:35 pm

    #670687

    This would normally be handled client side but what the heck :

     

    DECLARE @n AS Decimal(18,2)

    SET @n = 150

    SELECT RIGHT(REPLICATE('0', 13) + CONVERT(VARCHAR(15), @n), 13)

  • November 8, 2006 at 12:42 pm

    #670692

    might be a better way, i did it this way because of the decimal point:

    declare  @SomeValue        money

    Set @SomeValue=740002.64

    select right( '0000000000000' +  CONVERT(varchar,convert(int,(@SomeValue * 100))),13) AS FinalAnswer

    select right(REPLICATE('0', 13) +  CONVERT(varchar,convert(int,(@SomeValue * 100))),13) AS FinalAnswer

    Set @SomeValue=150.00

    select right( '0000000000000' +  CONVERT(varchar,convert(int,(@SomeValue * 100))),13) AS FinalAnswer

    select right( REPLICATE('0', 13) +  CONVERT(varchar,convert(int,(@SomeValue * 100))),13) AS FinalAnswer

    edited because RGR's got a better snippet.

    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!

  • November 8, 2006 at 12:43 pm

    #670693

    Thanks but it doesnot remove the decimal from the final value.

    For example I ran the above query with @n=740002.64

    SELECT RIGHT(REPLICATE('0', 13) + CONVERT(VARCHAR(15), 740002.64 ), 13)

    I got the result as '0000740002.64'. I want it without decimal  '000074000264'.

  • November 8, 2006 at 12:47 pm

    #670694

    Thanks. The second solution worked.

  • November 8, 2006 at 2:35 pm

    #670719

    Much shorter option:

    REPLACE(STR(@n*100, 13), ' ', '0')

    _____________
    Code for TallyGenerator

  • November 8, 2006 at 2:46 pm

    #670721

    Yup, thanx for the info .

Viewing 7 posts - 1 through 6 (of 6 total)

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