Search code examples
sql-serverunicodeinternationalizationcodepages

SQL-like expression can't find half space character (Zero-Width-Non-Joiner (ZWNJ))

In the code below I want to select token where this token contain half space character.

  Select *  from
     (select token = 'aaa‏‏sss') as dd
  where token like '%‏‏%'

favorable response:

    aaa‏‏sss

output response:

   null

notation: This character is a Persian character and not showed with a viewable mark. But this character separate string on two sides of itself. for example: token بهترین with half space is بهت‌رین

Solution

  • I think that the problem is about the collation.

    For example this query :

    select PATINDEX('%‏‏%','aaa‏‏sss' collate  Arabic_CI_AS)

    Return 1, but this one:

    select PATINDEX('%‏‏%','aaa‏‏sss' collate  SQL_Latin1_General_CP1_CI_AS)

    return 4; and this is the correct position of half-space character in input string.

    screenshot

    Therefor you must change the Collation of your input string to some Latin Collation like: SQL_Latin1_General_CP1_CI_AS