Selecting based on Thorn character MS SQL Server [duplicate]

Question

Consider the following table

created via:

CREATE TABLE WORD_TEST(WORD NVARCHAR(100));

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

Notice the Thorn (þ) character in the word puþpies.

Now if I want to do something like find all the rows which have this character in it I would try something like

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

Which gives the result

But the problem is that it also matches any 'th' in the words. I have also tried the following variations which yield the same result.

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

How can I select based only on the words that contain that character?

Answer 1

An alternative to Felix's suggestion to alter the SELECT statement would be to set the collation of the WORD column when creating the table so that it does not cast the thorn character to something else (apparently th in this case):

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

Now doing your SELECT statement should give you the expected results:

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