In APEX, SQL Commands, how do I replace a comma with a point?

Question

Hello!


I'm having a database inside Oracle APEX. No idea what type of database, but I presume 11g.
Apex version is 4.1.1.00.23.
I'm trying to replace comma's inside the data of a column with points.

For example, the kcal value has 24,5 and I want this to change to 24.5
So, comma turns into a point.

This is my code so far:
UPDATE FOODALL a
SET a.kcal = (
SELECT REPLACE(b.kcal, ',','.')
FROM FOODALLES_5_COPY b
WHERE b.kcal like '*')

This tells me 1360 row(s) were updated, but instead of changing the comma, all the values are now gone. Luckily for me I have backups from this table, so I can easily restore the lost data.

Now, I'm sure it should be possible. I tried the following code to see if the REPLACE is correct:
SELECT REPLACE (kcal, ',', '.')
FROM FOODALL

Before the above code, the column had data like this:
KCAL
120,00
85,00
84,00
125,00
157,00
220,00
73
100,00
188,00
337,00

After executing the code mentioned above, it returns data like this:
REPLACE(KCAL,',','.')
120.00
85.00
84.00
125.00
157.00
220.00
73
100.00
188.00
337.00

SO! Tada! It can replace the comma's with points! Now, why won't it work with the UPDATE statement?
What am I doing wrong?

Please, help me with this if you have an idea!
I've been trying for so long now, doing a search on the web didn't help me so far... I've been busy with normalizing a XLS sheet and importing it in the database (1360 rows, that's not small to normalize!), it was the first time I had to do this (I'm a trainee at Oracle) and I really need to find out how to properly change the comma's with points...

Also, getting rid of the WHERE clausule isn't an answer. Because then I get the error that a single-row subquery returns more than one row.



Thank you in advance for your answers!

Answer 1

Whoa!! hold on...



You have committed a logical error, wherein all the records of that table would be modified, beyond the replacement of the comma with a decimal.



You should be doing this, instead of yours -



UPDATE FOODALL a

SET a.kcal = REPLACE(a.kcal, ',','.')

WHERE a.kcal like '*'



No lets decipher & list out those logical errors of your query -



1. The usage of WHERE clause in your query, would yield no row ( based on the data posted by you, there's no record/ wouldn't be a record that has this "*"), implying no records in the result set. This would now be fed to the UPDATE query as NULL, and the UPDATE query in turn would set all the records to NULL in the table.



2. Your query can be modified as rightly working one this way -



UPDATE a

SET a.kcal = (

SELECT REPLACE(b.kcal, ',','.')

FROM FOODALL b

WHERE b.kcal =a.kcal

)

FROM FOODALL a



You are indirectly making a self join to ensure that only the relevant record gets updated. Beware that, based on the data the subquery could return multiple results there failing the query from execution. But, it was a demonstration of alternative.



And at last a free advice - Such updates has to be done by opening explicit TRANSACTIONS.



-- In 'thoughts'...

Lonely Rogue.

Answer 2

Before changing anything in your database, are you sure you need to ? In other words are those numbers really stored as strings ? You may be mistaken by the fact that numbers (in NUMBER) types are returned to you using commas instead of decimal points. That formatting is decided by the locale for your current session. If your locale is a French (or German, or pretty much any locale but US or UK), then any select will result in numbers being displayed using commas as decimal separator. But of course those commas are not stored anywhere. Another user that happens to use an american locale will see decimal points.



That said, and assuming you are dealing with strings, then the update is very simply this:



UPDATE FOODALL

SET kcal =REPLACE(kcal, ',','.');



followed or course by a



COMMIT,



And BTW, there is no reason to go back to any backup if your update is wrong, as long as you have not committed your transaction: just ROLLBACK and your data will be back the way it was;

Answer 3

A very common question, I am not in a position to test code out right now but I will give you two alternatives:



1) Try this instead, again untested:

UPDATE FOODALL a

SET a.kcal = (

SELECT REPLACE(b.kcal, ',','.')

FROM FOODALL b

WHERE b.kcal = a.kcal)



2) Take the WHERE clause out and then limit the amount of rows returned.