Oracle SQL replace

Question

Unfortunately I don't have the possibility to change field type.

I would like to REPLACE a , to . in a Typ=1 type of field (e.g.: 4,37 so in the end it should be 4.37), and I've tried CAST() and TO_NUMBER and TO_CHAR and I don't even know what else also, but I keep getting the ORA-01722 and it drives me crazy already. Why does it have to be a number for replacing ???

SELECT REPLACE(fmm, ',', '.') fmm FROM ...

Or do you have a better idea how can I do it without REPLACE maybe ?

UPDATE: it seems he has a problem with:

ORDER BY TO_NUMBER(fmm, '99D99')

So it seems he is taking the replaced version, so with . of fmm, but why ????

You mean with the replace() function? Or did you only try those conversion functions you mentioned? (Hopefully you don't also have periods in the values already, i.e. as group separators...) — Alex Poole
– Alex Poole, Commented Jul 19, 2019 at 11:25
So what happened with the replace() you've added? That can't throw ORA-01722, so what is the issue with it? That fragment works, so your problem seems to be elsewhere. — Alex Poole
– Alex Poole, Commented Jul 19, 2019 at 11:31
I don't understand the request either. You say you want to change '4,37' to '4.37'. You are showing the related query. So you know how to do that. Where is the problem? What is the question? — Thorsten Kettner
– Thorsten Kettner, Commented Jul 19, 2019 at 14:59
Just to confirm, 'Typ=1 type of field' means a varchar2 column? replace should do it, or regex_replace if it's more complicated than your example. What isn't working? — William Robertson
– William Robertson, Commented Jul 20, 2019 at 9:09

Barbaros Özhan · Accepted Answer · 2019-07-19 14:32:03Z

2

Try to remove the commas by replace(nvl(nr,0),',',''), and then formatting by

with tab as
(
 select '1,234,567' as nr
   from dual
)
select to_char( 
                replace(nvl(nr,0),',','') 
              ,'fm999G999G990','NLS_NUMERIC_CHARACTERS = '',.''')
       as "Number"
  from tab;

Number
----------
1.234.567

Demo

answered Jul 19, 2019 at 14:32

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2019-07-23 08:34:11Z

Passing a string (varchar2) value into the replace function cannot throw an ORA-01722.

it seems he has a problem with:
ORDER BY TO_NUMBER(fmm, '99D99')

If that's complaining when fnm is '4,37' then you could add a replace() call inside the to_number(), but it's simpler/clearer to specify the NLS_NUMERIC_CHARACTERS as part of the conversion, so it knows that D is represented by a comma, and doesn't rely on the session settings:

order by to_number(fnm, '99D99', 'NLS_NUMERIC_CHARACTERS=,.')

If your table has a mix of values with period and comma decimal separators then you need to fix the data - this is the main reason you should not be storing numbers as strings in the first place. If you can't fix the data then you can workaround it with replace(), but it isn't ideal; you can then use a fixed period as the decimal character:

order by to_number(replace(fnm, ',', '.'), '99.99');

or still specify NLS_NUMERIC_CHARACTERS:

order by to_number(replace(fnm, ',', '.'), '99D99', 'NLS_NUMERIC_CHARACTERS=.,')

Either way that is 'normalising' all the string to only have periods, with no commas; and that allows them all to be converted.

db<>fiddle

what I don't understand, if I do some changes in the SELECT to a field, how can it affect the ORDER BY section? fmm should still remain 4,37 and not 4.37 in the ORDER BY section, shouldn't it?

No, because you gave the column expression REPLACE(fmm, ',', '.') the alias fnm, which is the same as the original column name; and the order-by clause is the only place column aliases are allowed, where it masks the original table column. When you do:

ORDER BY TO_NUMBER(fmm, '99D99')

the fnm in that conversion is the value of the column expression aliased as fnm, and not the original table column.

You can still access the table column, but to do so you have to prefix it with table name or alias, as the column from expression from the select list takes precedence (which is implied but not stated clearly in the docs:

expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or materialized views in the FROM clause.

So you can either explicitly refer to the table column via the table name or, here, an alias:

SELECT REPLACE(t.fmm, ',', '.') fmm
FROM your_table t
ORDER BY TO_NUMBER(t.fmm, '99D99')

though you still shouldn't rely on the session NLS settings really, so can/should still specify the NLS option to match the table column format:

SELECT REPLACE(t.fmm, ',', '.') fmm
FROM your_table t
ORDER BY TO_NUMBER(t.fmm, '99D99', 'NLS_NUMERIC_CHARACTERS=,.')

or use the replaced value and specify the NLS option for that (notice the option itself is different):

SELECT REPLACE(fmm, ',', '.') fmm
FROM your_table
ORDER BY TO_NUMBER(fmm, '99D99', 'NLS_NUMERIC_CHARACTERS=.,')

db<>fiddle

If your table has a mix of period and comma values then you need to use the column-alias version so it is consistent when it tries to convert. If you you only have commas then you can use either. (But again, you shouldn't be storing numbers as strings in the first place...)

That's all clear, but what I don't understand, if I do some changes in the SELECT to a field, how can it affect the ORDER BY section? fmm should still remain 4,37 and not 4.37 in the ORDER BY section, no matter what I do with it in the SELECT section, shouldn't it? I guess in MySQL is working like this, isn't it?
@user2511599 - right, the two separate fragments of code in your question still kind of obscured the actual problem; I should have paid more attention to the last line after your update. I've edited my answer to explain the confusion over the column alias.

Collectives™ on Stack Overflow

Oracle SQL replace

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related