CODEWITHSUNDEEP
sqloracle-databasesql-update
hc0re
hc0re

Reputation: 1986

Update a VARCHAR2 column using a set of NUMBERS - how to force the decimal places?

I have an update SQL looking like this:

UPDATE DCR_TEMP cr SET cr.TITLE = cr.AMOUNT||'/'|| cr.CLIENT_NUMBER;

This should update the TITLE column with, for example: 12223.52/1234567

BUT, the data in columt AMOUNT can exist in 3 different versions, ie.:

  1. 23
  2. 23.1
  3. 23.13

But it is required, that the AMOUNT should be converted to:

  1. 23.00
  2. 23.10
  3. 23.13

There have to be two decimal places in the output, no matter which one of the 3. options of how the AMOUNT looks like.

How can I do this in SQL/Oracle?

Upvotes: 0

Views: 151

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

It is generally a bad idea to store data redundantly. If you can compute the title from amount and client_number, why store it at all?

However, numbers have no format. 23 = 23.0 = 23.00 = 00023.00000 . You'd use to_char to get a string containing the number formatted according to your wishes.

update dcr_temp set title = to_char(amount, 'fm9999999990D00') || '/' || client_number;

Upvotes: 2

Related Questions