Characters "ي" and "ی" and the difference in persian - Mysql

I'm working on a UTF-8 Persian website with integrated mysql database. All the content in the website are imported through an admin panel and it's all persian.

As you might know arabic language has the same letters as persian except some. The problem is when a person tries to type on a keyboard with arabic layout it writes "ي" as an character and if he tries to type by a keyboard with persian layout it types "ی" as character.

So if a person searches for 'بازی' the mysql won't find 'بازي' as the result.

Important Note: 'ی' is 开发者_Go百科not the only character with this property, there are lots of them and they are very similar.

How can I fix this issue?

One simple naive solution seems to be replace all "ي" with "ی" before importing the data into database, but i'm searching for a better robust solution than this.

Dear EBAG, We have a single Arabic block in Unicode which contains both Arabic & Persian characters.

06CC is Persian ی and 064A is Arabic ي

Default windows keyboard uses code page 1256 for arabic characters which put 064A as default ي for bothPersian and Arab users because Arab users are much more than Persian.

ISIRI make an standard keyboard ISIRI 9147 and put both Arabic and Persian Yeh on it but Perisan ی is the default characters. Persian users which are using standard keyboard will put ( and use ) standard Persian ی‍ while the rest of them use arabicي`.

As you told usually while we are saving a data to database we change arabic ي to Persian ‍ی and when we are reading from it we just go for Persian so everything is true.

the second approach is to use a JavaScript file in web application to control user input. most of the persian websites use this approach to save characters to database. In this method user don't need to install any Keyboard layout for Persian or Arabic keyboard. He/she just put the keyboard on English and then in JavaScript file developer check that which character is equevalent for him. Here you can find ISIRI 9147 javascript for web application and a Persian Guid to use it.

the Third approach is to use a On-Screen Keyboard that work just like the previous one with a user interface and is usually good for thise who are not familiar with Persian keyboard.

The forth approach is to search both dialect. As you know when you install MySql or SQL Server you can set the collation and also you have an option to support dialect ( and case sensivity). if you enable arabic collation with dialect you can get result for both of them and usually this works fine in sql server I don't test it in MySql. This is the best solution yet.

but if I were you, I implement a simple sql function which get nvarchar and return nvarchar. then I call it when I wanted to write data. and whenever you want to read, you can go for the standard one.

Sorry for the long tail.

update TABLENAME set COLUMNNAME=REPLACE(COLUMNNAME,NCHAR(1610),NCHAR(1740))

or

update TABLENAME set COLUMNNAME=REPLACE(COLUMNNAME,'ي',N'ی')

This is called a collation. It's what MySQL uses to compare two different characters. I'm afraid I don't know anything about persian or arabic, but the concept is the same. Essentially you've got two characters which map to the same base value. You need to find a collation which maps ي to ی. I'm afraid that's as helpful as I can be without knowing more about the language.

The first letter (ي) is Yāʾ in the arabic alphabet. The second letter (ی) is ye in the perso-arabic alphabet.

More on the perso-arabic alphabet here: http://en.wikipedia.org/wiki/Perso-Arabic_alphabet

"Two dots are removed in the final ye (ی). Arabic differentiates the final yāʾ with the two dots and the alif maqsura (except in Egyptian Arabic), which is written like a final yāʾ without two dots.

Because Persian drops the two dots in the final ye, the alif maqsura cannot be differentiated from the normal final ye. For example, the name Musâ (Moses) is written موسی. In the final letter in Musâ, Persian does not differentiate between ye or an alif maqsura."

Seems to be an interesting problem...

I was struggling with the similar situation 5-6 years ago, when Lucene was not an option for MySQL and there were no Sphinx (Never tried Sphinx result on this), but what I did was I found pretty much most of the possible alternations and put them in an array in PHP. So if the input keyword contained any of those characters, I generated all the possible alternates of that.

So for the input of 'بازی' I would have generated {'بازي' , 'بازی' } and then I would query the MySQL for both, like the simplest query below :

SELECT title,Describtion FROM Games WHERE Description LIKE '%بازي%' OR Description LIKE '%بازی%' 

The primary list of alternatives is not very long though.

If you've the possibility to switch DB engine, you might want to look into the full text search functionality of PostgreSQL:

http://www.postgresql.org/docs/9.0/static/textsearch.html

Among other things, you can configure it so that it indexes/searches unaccented characters, and you can define all sorts of additional dictionaries (e.g. stop words, thesaurus, synonyms, etc.).

If not, consider using Sphinx or Lucene instead of like statements for your searches.

I know answering this topic is like digging a corpse from its grave since it's really old but I'd like to share my experience IMHO, the best way is to wrap your request and apply your replacement . it's more portable than other ways. here is a java sample

public class FarsiRequestWrapper extends HttpServletRequestWrapper{

@Override
public String getParameter(String name) {
    String parameterValue = super.getParameter(name);
    parameterValue.replace("ی", "ي");       
    parameterValue.replace("\\s+", " ");
    parameterValue.replace("ک","ک");
    return parameter.trim();
}

}

then you only need to setup a filter servlet

public class FarsiFilter implements Filter{

public void doFilter(ServletRequest request, ServletResponse response,
        FilterChain chain) throws IOException, ServletException {

    HttpServletRequest req = (HttpServletRequest) request;
    FarsiRequestWrapper rw = new FarsiRequestWrapper(req);
    chain.doFilter(rw, response);
}

} although this approach only works in Java, I found it simpler and better.

You must use N (meaning uNicode) before non-English characters, for example:

REPLACE(COLUMNNAME, N'ي', N'ی')