Arabic FullText Search in SQL Server

Question

I have a problem in Arabic Full Text Search in SQL-Server, this link says that we can use Arabic in full text search.

To describe more, I've enabled full text search on one of my columns which contains Arabic and English texts.

When i use full text search for English inputs every thing works fine but in Arabic it doesn't.

please notice that i also test my queries with other tables that just have Arabic contents and again i got nothing.

Used queries are like this:

select * from tbl where freetext(title, '"*شمس*"')

SELECT * FROM sys.dm_fts_parser('"الاستنتاجات ونصائح للوقاية من أشعة الشمس"', 1025, 0, 0) WHERE special_term <> 'Noise Word' AND display_term IS NOT NULL

Masthead

How do you create full text catalog and index? – Immaterialize

@Immaterialize right click on table, full-text index, then define full-text index, i created a catalog and at the end i choose a table's column and clicked the next button. ( so i used wizard menu for creation ) – Legwork

If you have a lot of rows in table, it can take a time to index... Can you add whole sql-script? – Immaterialize

@Immaterialize i also check it with table which contains just 10 rows. I wrote all sql in my question, I've got nothing more to show – Legwork

Answer 1

I would recommend this approach:

1) Make sure your column in the database is nvarchar 2) Make sure you are inserting nvarchar value into the column.

Here is a small demo of why this is important. Probably you have one of the issues shown in this script.

CREATE TABLE ftstest (Id int PRIMARY KEY, name varchar(1000), nameA varchar(1000) COLLATE Arabic_BIN, nameN nvarchar(1000) )
GO

-- CREATE FTS index for the ftstest table using designer

INSERT INTO ftstest VALUES(1, N'"الاستنتاجات ونصائح للوقاية من أشعة الشمس"', N'"الاستنتاجات ونصائح للوقاية من أشعة الشمس"', N'"الاستنتاجات ونصائح للوقاية من أشعة الشمس"')
GO

INSERT INTO ftstest VALUES (2, '"الاستنتاجات ونصائح للوقاية من أشعة الشمس"', '"الاستنتاجات ونصائح للوقاية من أشعة الشمس"', '"الاستنتاجات ونصائح للوقاية من أشعة الشمس"')
GO

SELECT * FROM ftstest WHERE CONTAINS(name, N'"الاستنتاجات ونصائح للوقاية من أشعة الشمس*"') 
SELECT * FROM ftstest WHERE CONTAINS(nameA, N'"الاستنتاجات ونصائح للوقاية من أشعة الشمس*"') 
SELECT * FROM ftstest WHERE CONTAINS(nameN, N'"الاستنتاجات ونصائح للوقاية من أشعة الشمس*"') 
SELECT * FROM ftstest WHERE CONTAINS(name, '"الاستنتاجات ونصائح للوقاية من أشعة الشمس*"') 
SELECT * FROM ftstest WHERE CONTAINS(nameA, '"الاستنتاجات ونصائح للوقاية من أشعة الشمس*"') 
SELECT * FROM ftstest WHERE CONTAINS(nameN, '"الاستنتاجات ونصائح للوقاية من أشعة الشمس*"') 

The same issue with your second query. '' - means a varchar literal, N'' - means nvarchar. Try this query to check:

SELECT * FROM sys.dm_fts_parser(N'"الاستنتاجات ونصائح للوقاية من أشعة الشمس"', 1025, 0, 0) 

Hubby

thank you for your answer my second query after manipulation returns value but my first query still does not work, actually i need to use FullTextSearch with Arabic and just with passing شمس i want to get الاستنتاجات ونصائح للوقاية من أشعة الشمس, please notice that شمس is the root of الشمس. like go which is root of going or gone. – Legwork

@ElyasHadizadeh going can be found by go* predicate, but this does not mean searching by the root, this is just a wildcard search. If you want to search by the root, you should use FORMSOF predicate in the CONTAINS. Something like this N'FORMSOF(INFLECTIONAL, "شمس")' – Hubby

@ElyasHadizadeh in your case the search by the root might be impossible. As far as I see such root extract is not implemented in the engine. Try the following two queries to check: – Hubby

SELECT * FROM sys.dm_fts_parser(N'FORMSOF(INFLECTIONAL, "going")', 1033, NULL, 0) – Hubby

SELECT * FROM sys.dm_fts_parser(N'FORMSOF(INFLECTIONAL, "الشمس")', 1025, NULL, 0) – Hubby