sqloracle-databasestringoracle10gora-01403

REPLACE in Oracle 10g not working as I expected, can anyone explain?

I am using oracle 10g express.

I have a table named artists_i_hate, I have simplified to illustrate the problem clearer

ID | Name       | Opinion
-----------------------------
11 | jamesblunt | i hate him

I run the statement 

SELECT * FROM artists_i_hate WHERE to_char(ID)=REPLACE(to_char(1.1), '.');

Why do I get 'no data found' I can't find an explanation anywhere in the documentation.

btw I am aware that the following works: 

SELECT * FROM artists_i_hate WHERE to_char(ID)=REGEXP_REPLACE(to_char(1.1), '[^0-9]');

So I am thinking the other statement doesn't work because it doesn't like replacing certain symbols.

edit:

Pending testing on original environment having read first 2 responses

Solution

  • It may depend on NLS settings as, in some languages the . is not the decimal separator so to_char(1.1) would NOT give '1.1'

    SQL> alter session set nls_numeric_characters = ',.';

Session altered.

SQL> select to_char(12.34) from dual;

TO_CH
-----
12,34

    In which case the REPLACE wouldn't change anything and therefore the ID wouldn't match.

    PS. If this is the issue, one fix would be

    select to_char(1.25,'999.99','NLS_NUMERIC_CHARACTERS=.,') FROM DUAL