Geek Answers Handbook

Oracle regex with hyphen does not produce the same result on Windows as on Unix

I have the following regular expression query:

select REGEXP_REPLACE ('TEST 3304 V2', '[`~!@#$%^&*()_+-={}|;.:<>?,./]', ' ') as REG 
from dual;

When executed through SQL * Plus on a Windows machine , it returns the following:

SQL>  select REGEXP_REPLACE ('TEST 3304 V2', '[`~!@#$%^&*()_+-={}|;.:<>?,./]', ' ') as REG from dual;

REG
------------
TEST 3304 V2

On a SunOS machine, I get a different result:

SQL>  select REGEXP_REPLACE ('TEST 3304 V2', '[`~!@#$%^&*()_+-={}|;.:<>?,./]', ' ') as REG from dual;

REG
------------
TEST      V

These queries were executed with the same Oracle server. Is there a reason for this output difference?

Windows * Plus Version for Windows:

SQL*Plus: Release 11.2.0.1.0 Production on Mar. Oct. 14 15:36:35 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Unix SQL * Plus Version:

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 14 16:01:26 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
+4
source share
1 answer

, . , -, , , NLS_LANG, NLS_SORT.

NLS_LANG=ENGLISH_UNITED KINGDOM.WE8ISO8859P1:

SQL> select REGEXP_REPLACE ('TEST 3304 V2', '[`~!@#$%^&*()_+-={}|;.:<>?,./]', ' ') as REG from dual;

REG
------------
TEST      V

SQL> select value from nls_session_parameters where parameter = 'NLS_SORT';

VALUE
----------
BINARY

, , , NLS_LANG="ARABIC_MOROCCO.AR8MSWIN1256":

SQL> select REGEXP_REPLACE ('TEST 3304 V2', '[`~!@#$%^&*()_+-={}|;.:<>?,./]', ' ') as REG from dual;

REG
------------
TEST 3304 V2

SQL> select value from nls_session_parameters where parameter = 'NLS_SORT';

VALUE
----------
ARABIC

, +-= , + =. ISO8859-1 Windows 1252, 43 61, - 48 - , . Windows 1256. ( , ASCII).

NLS_LANG BINARY ARABIC, . ; NLS_LANG=ENGLISH_UNITED KINGDOM.WE8ISO8859P1:

SQL> select REGEXP_REPLACE ('TEST 3304 V2', '[`~!@#$%^&*()_+-={}|;.:<>?,./]', ' ') as REG from dual;

REG
------------
TEST      V

SQL> alter session set NLS_SORT=ARABIC;

Session altered.

SQL> select REGEXP_REPLACE ('TEST 3304 V2', '[`~!@#$%^&*()_+-={}|;.:<>?,./]', ' ') as REG from dual;

REG
------------
TEST 3304 V2

, , ; +-= +-3, , :

SQL> alter session set NLS_SORT=BINARY;

Session altered.

SQL> select REGEXP_REPLACE ('TEST 3304 V2', '[`~!@#$%^&*()_+-3{}|;.:<>?,./]', ' ') as REG from dual;

REG
------------
TEST    4 V

.

NLS, , , , , ; , .

+4

More articles:


All Articles