CODEWITHSUNDEEP
sqloracle-databasestringoracle10gora-01403
rogermushroom
rogermushroom

Reputation: 5586

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

Upvotes: 2

Views: 1379

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

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

Upvotes: 5

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

This below shows that there is absolutely no difference between the two expressions. They are completely equivalent so if the REGEXP_REPLACE works, so will the REPLACE.

CREATE TABLE tester2 AS
SELECT
    REGEXP_REPLACE(to_char(1.1), '[^0-9]') Col1,
    REPLACE(to_char(1.1), '.') Col2
from dual;

select * from tester2;    
select * from USER_TAB_COLUMNS where table_name = 'TESTER2';

Output:

COL1    COL2
11    11

TABLE_NAME COLUMN_NAME DATA_TYPE  DATA_LENGTH   
TESTER2 COL1        VARCHAR2   2
TESTER2 COL2        VARCHAR2   2

Upvotes: 1

Related Questions