Thorn MS SQL Server Symbol Selection

Question

Consider the following table

created through:

CREATE TABLE WORD_TEST(WORD NVARCHAR(100)); INSERT INTO WORD_TEST(WORD) VALUES('these'),('are'),('some'),('test'),('words'),('including'),('puþpies'),('with'),('parents'); 

Note the Thorn (þ) symbol in puþpies.

Now, if I want to do something like search all the lines that have this character, I would try something like

 SELECT * FROM WORD_TEST WHERE WORD LIKE '%þ%'; 

What gives the result

But the problem is that it also matches any "th" in words. I also tried the following options that give the same result.

 SELECT * FROM WORD_TEST WHERE WORD LIKE N'%þ%'; SELECT * FROM WORD_TEST WHERE WORD LIKE '%' + NCHAR(254) + '%'; 

How can I choose based only on words that contain this symbol?

Answer 1

An alternative to Felix’s suggestion to change the SELECT would be to set the WORD column to be mapped when creating the table so that it does not pass the spike character to something else (apparently th in this case):

 CREATE TABLE WORD_TEST(WORD NVARCHAR(100) COLLATE Latin1_General_100_BIN2); 

Your SELECT should now give you the expected results:

 SELECT * FROM WORD_TEST WHERE WORD LIKE '%þ%';