Hello,
the task is to find all entrys in table product in coloumn txtvi [defined as: VARCHAR2(40 BYTE)] where the string not only consist of these characters:
-_# .,0-9a-zA-ZäöüÄÖÜ()
The result set must be rows with not allowed characters.
Example "Ziel/Ergebnis" is not allowed; the '/' is not in the defined characters above
DB-Version: 10.2.0.4.0
NLS-Parameters are set to:
NLS_LANGUAGE AMERICAN
NLS_CHARACTERSET UTF8
First try was to use regular expression in sqlplus:
select txtvi from product where not regexp_like(txtvi,'^[-_# .,0-9a-zA-ZäöüÄÖÜ()]+$') order by txtvi;
Every combination in the regexp_like did not give the correct result. In all result sets I found allowed strings with german umlauts.
Could be that the problem?
Now I started to look for all strings with the german umlaut 'ä' only.
I knew there are several entrys with 'ä'. One example: '%Fahrgastzähler%'
I started sqlplus in a kornshell on AIX 6.1 ($nls_lang=AMERICAN_AMERICA.UTF8)
to find all strings containing the german umlaut ä:
SQL> select txtvi from product where txtvi like '%ä%';
ERROR:
ORA-01756: quoted string not properly terminated
I thought to have a more simple try:
SQL> select 'ä' from dual;
ERROR:
ORA-01756: quoted string not properly terminated
With 'ö'
SQL> select 'ö' from dual;
'
-
ö
With
SQL> select text from product where text like '%zähl%';
TXTVI
----------------------------------------
#27N0 Fahrgastzählung
Untersuchung Fahrgastzähler
Great, there is a result. But it not a solution. I will find all rows with german umlauts.
Why is there an error ora-01756? I get the same error with the german ß, but ü works like ö.
Only a problem in 10.2?
One more trial in DB-Version: 11.2.0.3.0 with NLS_CHARACTERSET AL32UTF8
and sqlplus ($nls_lang=AMERICAN_AMERICA.UTF8)
SQL> select 'ö' from dual;
'�'
---------
�
SQL> select 'ä' from dual;
ERROR:
ORA-01756: quoted string not properly terminated
No use of sqldeveloper possible:
The sqldeveloper on a Win7 Client did not display the german umlauts correctly. The result set for german umlauts is every time empty.
Yes, there is a problem with german umlauts.
Any idea how this problem can be solved?