Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    2

    Unanswered: Converting a decimal

    I need to convert a value 5.300000000 to 0005300. Basically I want to remove the decimal point, pad the out put with a couple of zeros and return a value that is 7 bytes long. I may need to convert something like 10.300000000 to be 0010300.

    Anyone have any thoughts?

    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    DECLARE @x decimal(5,2)
    SELECT @x = 5.3
    SELECT @x, RIGHT(REPLICATE('0',7)+REPLACE(CONVERT(varchar(7),@x * 10),'.',''),7)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    Hi,
    Here is a little code that should do it for you.
    Granted it is ugly but it works


    declare @d decimal (16,9)
    set @d = 9.3000

    print Replicate('0', 7 - len ( replace( Left(@d,Charindex('.',Cast(@d as Varchar))+3),'.','')))+ replace(Left(@d,Charindex('.',Cast(@d as varchar))+3),'.','')


    Hope that Helps

    Tal McMahon

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    I have a function for zero-padding, which cleans things up a bit in my queries:

    CREATE FUNCTION [dbo].[fn_zero_pad]
    (@string_data VARCHAR(100),
    @new_length INT)
    RETURNS VARCHAR(100) AS
    BEGIN

    RETURN REPLICATE('0', @new_length - LEN(@string_data)) + @string_data

    END
    -bpd

Posting Permissions

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