Problem with U+02BB character in SQL server LIKE search

Question

My SQL table has rows like

Hawaiʻi 
01
abcʻdef
ʻabc
abcʻ

All "apostrophes" are actually okina characters (U+02BB)

When I use the following search statement

select text01 from atable where text01 like N'%ʻ%'

I get all rows returned, even "01"

ESCAPE clause (e.g. LIKE N'%!ʻ%' ESCAPE '!') does not help

SQL Server 2005 SP3

Any ideas?

Answer 1

Perhaps try CHARINDEX, like so:

DECLARE @FindThis nchar(1)
SET @FindThis =  --  err, however you get this particular character into a variable

SELECT text01 from atable where charindex(@FindThis, text01) > 0

Answer 2

Assuming your okina is represented by NCHAR(96) (which I think is right), then you have strings like this:

select N'Hawai' + nchar(96) + N'i'

Then a like expression would be:

select case 
  when ( N'Hawai' + nchar(96) + N'i' ) like ( N'%' + NCHAR(96) + N'%' )
  then 'Found okina!'
  else 'D''OH!' 
  end

Mele Kalikimaka ame Hauoli Makahiki Hou!

Answer 3

This only gets one result as well in my test (web browser custom interface to MSSQL, I don't have Enterprise Manager handy at the moment, on my Mac):

select text01 from atable where text01 like N'%[ʻ]%'

Have you verified that the code creating the SQL statement and passing it along is properly sending the right unicode character?

Try this:

SELECT UNICODE('ʻ')

Or, it may be a collation issue... the collation setting has some impact on string comparisons.