1

I have a SQL Server 2012 DB with a column that includes Arabic text. The column is nvarchar(MAX) with collation Arabic_CI_AI_KS_WS. To test if LIKE searches are working I copied some of the text from the column and pasted it into a query as follows

select * from MyTable where MyColumn like N'%arabic_text%'

I can visually see the text in the column, but the query returns no results. Searching for latin characters in the column works fine.

What else can I try?

Improve this question
4
  • 2
    1) try using Arabic_100_CI_AI_KS_WS instead as it is newer and more likely to have proper sort weights defined. 2) please post some example strings from your column for us to test with.
    – Solomon Rutzky
    Aug 15, 2018 at 14:09
  • @SolomonRutzky I'm not allowed to share the actual data. I can tell you the table is full of snapshots of web pages including the full HTML source and a bunch of metadata. The column that's not working is the full HTML source column. It does work if I put the exact same raw text in with no HTML.
    – just.another.programmer
    Aug 15, 2018 at 15:01
  • 1
    ? You don't have a single value in the table that you can jumble up and provide us with a repro? Seriously? It seems like you only need a string with a single character embedded. How a single character could be considered proprietary, secret data, I have no idea. "Hey Verizon, when I call a certain number, I get disconnected." Verizon: "Can you tell us a number that this happens with?" Me: "No." Verizon: "¯\_(ツ)_/¯"
    – Aaron Bertrand
    Aug 15, 2018 at 15:21
  • So you can't post one value from that HTML source column? Can you reproduce the problem with a subset of the actual data in that column, and post that subset? Also, regarding trying other Collations, you would add the following to the end of your query: COLLATE Arabic_100_CI_AI_KS_WS_SC. Also try: COLLATE Arabic_100_BIN2.
    – Solomon Rutzky
    Aug 15, 2018 at 15:31

1 Answer 1

Reset to default
-3

Maybe it is too late for an answer, but I suggest the following solution:

"SELECT * FROM MyTable 
WHERE MyColumn LIKE N'" & MYTEXTBOX & "%';"

In this context MYTEXTBOX is not a built in keyword in the query; it is an external variable. I use this solution always and it works, especially when building a complex search criteria, and it work with all versions.

Improve this answer
1
  • 1
    Creating dynamic SQL by concatenation is vulnerable to SQL injections, so it should be avoided. What's more, this doesn't really address the issue why looking for Arabic text fails.
    – vonPryz
    Dec 9, 2021 at 13:06

Not the answer you're looking for? Browse other questions tagged or ask your own question.