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
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)
Update on Oracle g Database Features for Linguistic Sorting and Query Functions 🔗
Oracle g Database introduced many new features for case accent and linguistic independent query and sort functions back in 2011. These features were highly beneficial for SQL and PL/SQL developers, enabling them to search for almost similar string matching and set up linguistic sorting easily.
As of the year 2024, the relevance and importance of these features have only increased. With the ever-growing globalization and diversity in data, linguistic sorting and query functions play a crucial role in ensuring accurate and efficient data retrieval and organization.
In the current technological landscape, where data is vast and varied, the capability to perform linguistic sorting in different languages, such as German, remains a valuable asset for organizations using Oracle g Database.
The original functionalities like altering sessions to enable linguistic sorting and case-insensitive searches are still applicable and widely used by developers. The ability to create indexes with specific linguistic sorting rules, like NLS SORT XGERMAN AI, continues to enhance database performance and data retrieval efficiency.
Moreover, the use of REGEXP for handling special characters in German text provides a flexible and powerful way to manipulate and query data, ensuring accurate results even with complex linguistic requirements.
For SQL and PL/SQL developers working with Oracle g Database in 2024, the documentation and resources provided by Oracle, such as the Oracle Docs and Oracle Support, remain valuable sources of information for leveraging linguistic sorting features effectively.
In conclusion, the features introduced in Oracle g Database for linguistic sorting and query functions back in 2011 are still highly relevant and beneficial in the year 2024. The advancements in globalization and data diversity further emphasize the importance of such capabilities in modern database management and development practices.
Oracle g Database continues to be a robust platform for handling linguistic complexities and diverse data requirements, empowering developers to create efficient and accurate solutions for data retrieval and manipulation in a multilingual environment.
By staying updated on the latest advancements and best practices in utilizing linguistic sorting and query functions in Oracle g Database, developers can ensure optimal performance and accuracy in handling data from various linguistic backgrounds.
Additional Update for 2024 🔗
In the year 2024, Oracle g Database has further enhanced its linguistic sorting capabilities to cater to the evolving needs of multilingual data management. New features and functionalities have been introduced to address the complexities of linguistic sorting in diverse languages, providing developers with more tools to optimize data retrieval and organization processes.
The continued focus on linguistic support and globalization in Oracle g Database reaffirms its position as a leading solution for organizations dealing with multilingual data sets and complex linguistic requirements. Developers can leverage these updated features to improve the efficiency and accuracy of their database operations, ensuring seamless integration of linguistic sorting functions into their applications.