Results 1 to 11 of 11

Thread: Char Format

  1. #1
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46

    Unanswered: Char Format

    I have one table that has a column 'amount' with datatype 'numeric(19,5)'
    I'm want to insert the data from this column into another column (in a different database) with this format: '00000000.00' as 'char'
    example: If in the first column I have 800.75864 I want to insert it in the other column as '00000800.75'

    any suggestions on how to make this conversion?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This is freaking blowing my mind...

    This should work
    Code:
    DECLARE @x decimal(15,5), @y char(11)
    SELECT @x = 800.75864 
    SELECT @x, RIGHT(REPLICATE('0',11)+CONVERT(char(11),CONVERT(decimal(15,2),@x)),11)
    But's it's acting like it's doing an implicint conversion to numeric...

    Hold on..

    If I do...

    Code:
    DECLARE @x decimal(15,5), @y char(11)
    SELECT @x = 800.75864 
    SELECT @x, REPLICATE('0',11)+CONVERT(char(11),@x)
    Last edited by Brett Kaiser; 03-08-04 at 13:50.
    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
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you just need the rounding, then check the round function. If you need zero padding too (which is rather silly in most cases, since SQL Server handles all of the "leading zero" issues for you), then you'll probably have to resort to a user defined function.

    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Methinks thou meant:
    PHP Code:
    DECLARE @x decimal(15,5), @y char(11)
    SELECT @800.75864 
    SELECT 
    @xRIGHT(REPLICATE('0',11)+CONVERT(varchar(11),CONVERT(decimal(15,2),@x)),11
    That works, as long as the value to convert is positive... Then you get some "interesting" results as the sign floats about!

    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah...what an idiot....

    char(11)...

    just found the prob....

    Thanks...
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It is the "little" things that make the biggest boom when you trip over them!

    -PatP

  7. #7
    Join Date
    Mar 2004
    Posts
    45
    declare @n numeric(19,5)
    set @n = 800.75864
    SELECT REPLACE(STR(@n-.005,11,2),' ','0')

    Hans.

  8. #8
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    THANKS A LOT! IT WORKED

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by diegocro
    THANKS A LOT! IT WORKED
    What did you expect?

    And which one did you use?
    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.

  10. #10
    Join Date
    Feb 2004
    Location
    El Salvador
    Posts
    46
    Both, I've been trying to do this since yestarday, that's why I was happy.

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well that'll learn ya
    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.

Posting Permissions

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