Forum Stats

  • 3,750,551 Users
  • 2,250,192 Discussions
  • 7,867,005 Comments

Discussions

Query Help to append 0

Ricky007
Ricky007 Member Posts: 592 Bronze Badge

Dear Experts


Please help to append 0 at the end the value,after decimal i want to 4 values.


75.15

65.123

35.8


Expected Result

75.1500

65.1230

35.8000

Answers

  • Paulzip
    Paulzip Member Posts: 8,407 Blue Diamond
    edited Jul 23, 2021 5:12PM

    to_char(<number>, 'FM999999999999999999D0000')

    D = Decimal point symbol, alternatively use . if you know that locale uses a decimal point and not comma.

    FM = Fill mode, suppresses spaces

    0 = If no number there, put a zero in that place

    Lots of 9s prevent display of a # where number exceeds formatting

  • Ricky007
    Ricky007 Member Posts: 592 Bronze Badge

    Thank u so much,minor change in the requirement


    in column data stored as

    75.15,46.86

    65.123,96.638

    35.8,67.5



    Expected result

    75.1500,46.8600

    65.1230,96.6380

    35.800,67.500



    please help

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,890 Red Diamond

    Hi, @Ricky007

    minor change in the requirement

    Actually, that's a major change in the requirements, but whether it's minor or major, you shouldn't change the requirements after starting a thread. You'll get better results faster if you decide what you want before posting anything, then say what you want in the first message.

    If str is a string containing decimal points, then

    REGEXP_REPLACE ( REGEXP_REPLACE ( str
        	    	 		, '(\.\d*)'
    	    			, '\10000'
    	    			)
        	      , '(\.\d{4})\d*'
        	      , '\1'
        	      )
    

    returns a copy of str where each decimal point is followed by exactly four digits.

    The inner REGEXP_REPLACE adds '0's, so that each decimal point is followed by at least four digits The outer REGEXP_REPLACE removes digits, so each decimal point is followed by exactly four digits.

    There can be any number of decimal points (not just two) in str.

    If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test it.

  • mathguy
    mathguy Member Posts: 9,968 Gold Crown

    This is a pretty nonsensical question. Why don't you format the numbers before concatenating them into a comma-separated string, instead of doing it after the fact?

    Assuming you have a good reason (which I doubt, but let's go with that), you need to provide more details. For example, can there be negative numbers in the inputs? How about integers with no decimal point, such as 8? Can you have numbers with more than four decimal places - and if so, how should that be handled? Truncate, or round to four decimal places, or keep as is with the original number of decimal places?