5

Consider the following table

Words 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

enter image description here

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?

Java Devil
  • 10,629
  • 7
  • 33
  • 48

1 Answers1

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 '%þ%';
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks, Yes the collation does the trick, unfortunately I can't change the creation of the table, only added it for testing/showing purposes. However adding it to the selection criteria as Felix pointed out does solve the problem. – Java Devil Mar 10 '16 at 01:34