It will take approximately 3 minutes for you to read this post.

Gather up gang, I will give you the most suitable fit for the Turkish character problem for Oracle Databases.

It appears that Turkish character problem being experienced by most of the developers who ever touch this (SQL) part of the world.

I, personally experienced same problem, and needed to dive really really hard to find my own answer.

Let me describe you the situation first.

Turkish characters are pain in the ass, if you are trying on your database.

The Great “İ” problem

Dotted Great İ is in the Turkish characters, and it takes space in almost %70 of the Turkish words.

Let me give you an example.

Internet

You know what it is. “Internet” in English.

Now take a look at below one.

İnternet

Weird right?

This is saying “internet” in Turkish.

Normally, when you try to make a search In your database, you can make it in UPPER or LOWER method, like in below line:

SELECT UPPER('İIiı') FROM dual;

The problem here is, UPPER method doesn’t work for already UPPERED characters.

For example above query will return like below:

İIII

As you might realize, Great Dotted İ didn’t change.

So your search will fail to show you the right content, just because “İnternet” will be re-shaped as “İNTERNET”.

Which means that, while your databased data is “İNTERNET”, you are searching it as “INTERNET”.

FAIL

What is the solution?

NLS_UPPER()

NLS_UPPER Method is a savior. It is handling all the weird characters in all languages by filters.

Hmm, sounds cool. Let’s give it a try.

SELECT NLS_UPPER('İIiı', 'NLS_SORT = XTURKISH') FROM dual;

What will it return?

İIİI

Hmm, uppering Great Dotted İ reamins same, middle one getting bigger as his older brother, Great Dotted İ. “ı” is re-shaping as “I” which is normal, and great “I” remains itself, which is normal again.

Hurray!!!!This is the solution, If I make my search on both sides with NLS_UPPER Method, my problem will be solved, in any condition, right?

Right?

RIGHT?

You should see LOBS!

UPPER methods in CLOB

Nope, NLS_UPPER doesn’t work in CLOBS.

A CLOB is used to store unicode character-based data, such as large documents in any character set.

You can think them as closed boxes which you can see their data’s, but can not manipulate them as easy as regular datas.

So now, this leads us to the same situation again. What will I use to solve Turkish Character Problem if I use CLOB in my Database?

TRANSLATE

Translate method basically works as translating some unwanted parts of the data.

How it works?

Here is an example:

SELECT TRANSLATE('orcunyilmazcom', 'uia', 'UİA') FROM dual;

Will return as below:

orcUnyİlmAzcom

So yes, finally HURRAY!!!!!!!!!!!!!!

How we will use this?

I am giving you direct answer:

Use below query:

SELECT TRANSLATE('YOURQUERY', 'ıİ', 'ii') FROM dual;

Now have fun with Sludge Bombs :)