With Oracle10g Database many new features on case-, accent- and linguistic idependent query and sort functions

Some really nice additional functionallty for sql and pl/sql developers has been shipped with Oracle10g.

Ever tried to search for almost similar string matching?

With Oracle10g’s new database linguistic sort functions you can do it!

How to set up / enable linguistic sorting

alter session set NLS_COMP=ANSI;

alter session set NLS_SORT=GENERIC_BASELETTER;

Burleson Consulting – Oracle10g case insensitive index searches

ALTER SESSION SET NLS_COMP=ANSI;
ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;

http://sql-tips.de/index.php/Gro%C3%9F-/Kleinschreibung_bei_der_Suche_ignorieren

ALTER SESSION SET NLS_SORT='GENERIC_M_AI';

http://forums.oracle.com/forums/thread.jspa?messageID=1325991

About linguistic sorting on Oracle Docs

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96529/ch4.htm
http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10749/ch5lingsort.htm

Oracle Database Globalization Support Guide 10g

http://download.oracle.com/docs/cd/B14117_01/server.101/b10749.pdf

With PL/SQL

BEGIN
EXECUTE immediate 'ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER';
EXECUTE immediate 'ALTER SESSION SET NLS_COMP=ANSI';
-- your code
END;

http://www.dbforums.com/archive/index.php/t-707417.html

Linguistic sorting in German language (Linguistische Suche in Deutsch)

ALTER SESSION SET NLS_SORT = XGERMAN;
Select upper('Größe') from dual;
===> GRÖSSE

Herrmann & Lenz – Oracle10g

NLS_SORT=GERMAN
===> a,b,c…z
NLS_SORT=GERMAN_CI :
===> a,A,b,B,c,C…z,Z

http://www.hi-tech.co.il/college/ftproot/marathon/10gDevelopers.pdf

Alternatives: Special Chars in German with REGEXP (Spezialzeichen in Deutsch – eine Alternative)

select peid
, trim(replace(replace(replace(upper(name),'Ö','OE'),'Ä','AE'),'Ü','UE')) as name
, trim(replace(replace(replace(upper(vorname),'Ö','OE'),'Ä','AE'),'Ü','UE')) as vorname
, geb_Gruend_Datum as gebdat
,'x' as geschl
from daten

CREATE INDEX i_nls_1 ON daten(NLSSORT(name, 'NLS_SORT=XGERMAN_AI'));
CREATE INDEX i_nls_2 ON daten(NLSSORT(vorname, 'NLS_SORT=XGERMAN_AI'));

-- match auf nur erster vorname, eindeutig
WHERE a.vorname = regexp_substr(l.vorname,'^[a-zA-Z]*')

-- match auf nur erster name, eindeutig
WHERE regexp_substr(a.name,'^[a-zA-Z]*') = regexp_substr(l.name,'^[a-zA-Z]*')

-- match auf nur erster name und erster vorname, eindeutig
WHERE regexp_substr(a.name,'^[a-zA-Z]*')    = regexp_substr(l.name,'^[a-zA-Z]*')

-- match auf soundex erster name und erster vorname
WHERE soundex(regexp_substr(l.name,'^[a-zA-Z]*'))
    = soundex(regexp_substr(a.name,'^[a-zA-Z]*'))

-- match auf zweiter vorname(X) auf erster vorname(Y) !
WHERE regexp_substr(l.name,'^[a-zA-Z]*') = regexp_substr(a.name,'^[a-zA-Z]*')

-- match auf irgendwas ähnliches (something similiar, almost similiar)
WHRERE regexp_like(a.vorname,l.vorname)