SQL Server search in nvarchar & ntext

Question

I'm using SQL Server 2008 as my database engine in a VS2010, C# ASP.NET web app. My project is Farsi (Persian) so I've used `nvarchar` and `ntext` as my data types. I use following query to find rows from my database but nothing is returned, while I have a row with the specified keyword. Of course my keyword is in Persian (unicode). What is going wrong here? Is it because of using Farsi language? How can I search in `nvarchar` and `ntext` columns containing unicode characters? ``` myCommand = new SqlCommand("select * from tblArticle where name LIKE '%" + txtSearch.Text + "%'", SQLConnection); ```

Accepted Answer

You should use the `N'` prefix to indicate that you're searching for a Unicode string:

```
SELECT * FROM dbo.tblArticle WHERE name LIKE N'%......%'
```

Otherwise, you're converting your search string back to non-Unicode and then searching....

SQL Server search in nvarchar & ntext

Solution

Problem