Query Help to append 0

Ricky007

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

Paulzip

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

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

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

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?