locked
Problem Search In SQL Collation Persian RRS feed

  • Question

  • Hi,

    I have One problem with my SQL server  2008 R, I use Collation Persian_100_Bin and SQL save my Data Correctly.

    but When Search some Of word in Table Can Find Other word but can't Find these words "ی" and "ک"

    my Program I wrote Can search word by word.

    for example I Search "محمدی" in my program my Program can find this "محمد" and I can See "محمدی" in my table but when I add this "ی" to End of word

    search will Clean.

    Can Fix this Problem?

    tahnks

    Sunday, April 15, 2012 11:45 AM

Answers

  • The ntext data type is deprecated (you should use nvarchar(MAX) instead in SQL 2005 and later) but it returns the expected data in the example below:

    CREATE TABLE dbo.TestCollation(
    	Col_text text COLLATE Persian_100_BIN
    	,Col_ntext ntext COLLATE Persian_100_BIN
    	);
    
    INSERT INTO dbo.TestCollation VALUES(N'ی', N'ی');
    
    --this does not return the row
    SELECT * FROM dbo.TestCollation 
    WHERE Col_text LIKE N'ی' COLLATE Persian_100_BIN;
    
    --this returns the row
    SELECT * FROM dbo.TestCollation 
    WHERE Col_ntext LIKE N'ی' COLLATE Persian_100_BIN;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Peja Tao Tuesday, April 17, 2012 2:20 AM
    • Marked as answer by Peja Tao Monday, April 23, 2012 2:17 AM
    Monday, April 16, 2012 11:19 AM

All replies

  • Hi,

    I there is a person with the specified name and you think the problem is from collation you can use in your where condition

    WHERE name = '' COLLATE your_Collate here

    http://www.sqlmag.com/blog/practical-sql-server-45/tsql/Collation-SQL-Server-139576

    I hope this is helpful.


    Please Mark it as Answered if it answered your question
    OR mark it as Helpful if it help you to solve your problem
    Elmozamil Elamir Hamid

    MyBlog

    Sunday, April 15, 2012 2:08 PM
  • Are you using the varchar data type?  I suggest you use nvarchar instead of varchar as the script below illustrates.  You don't need the collate clause if the database default collation is Persian_100_BIN.

    CREATE TABLE dbo.TestCollation(
    	Col_char char(1) COLLATE Persian_100_BIN
    	,Col_nchar nchar(1) COLLATE Persian_100_BIN
    	);
    
    INSERT INTO dbo.TestCollation VALUES(N'ی', N'ی');
    
    --this does not return the row
    SELECT * FROM dbo.TestCollation 
    WHERE Col_char = N'ی' COLLATE Persian_100_BIN;
    
    --this returns the row
    SELECT * FROM dbo.TestCollation 
    WHERE Col_nchar = N'ی' COLLATE Persian_100_BIN;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, April 15, 2012 3:53 PM
  • hi.

    It is Ntext I use in SQL you think it is problem?

    Monday, April 16, 2012 7:19 AM
  • The ntext data type is deprecated (you should use nvarchar(MAX) instead in SQL 2005 and later) but it returns the expected data in the example below:

    CREATE TABLE dbo.TestCollation(
    	Col_text text COLLATE Persian_100_BIN
    	,Col_ntext ntext COLLATE Persian_100_BIN
    	);
    
    INSERT INTO dbo.TestCollation VALUES(N'ی', N'ی');
    
    --this does not return the row
    SELECT * FROM dbo.TestCollation 
    WHERE Col_text LIKE N'ی' COLLATE Persian_100_BIN;
    
    --this returns the row
    SELECT * FROM dbo.TestCollation 
    WHERE Col_ntext LIKE N'ی' COLLATE Persian_100_BIN;


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Peja Tao Tuesday, April 17, 2012 2:20 AM
    • Marked as answer by Peja Tao Monday, April 23, 2012 2:17 AM
    Monday, April 16, 2012 11:19 AM