Database Administrators Asked by Hooman Valibeigi on November 5, 2021
create table "Foo"
(
"id" integer identity (1000, 1) not null,
"name" nvarchar(100) not null
)
alter table "Foo" add constraint "Foo_PK" primary key ("id");
create fulltext index on "Foo" ("name" language 0x0) key index "Foo_PK" on "catalog" with stoplist = off;
The following query returns no rows
select * from "Foo" where contains("name", ' "پ*" ') and "id" > 1082;
While there are 26 rows matched by contains("name", ' "پ*" ')
and 87 matched by "id" > 1082
individually. The following two queries have 23 rows in common, oddly, their intersection returns no rows.
select * from "Foo" where contains("name", ' "پ*" ');
select * from "Foo" where "id" > 1082;
And more strangely, the following query returns exactly 23 rows. All "id"s are above 1082.
select * from "Foo" where contains("name", ' "پ*" ') and "id" > 1081;
Someone please explain what is going on in SQL Server.
I could fix it simply by dropping and creating the index again. Still don't know why it happened and how to prevent it from happening in the future.
Answered by Hooman Valibeigi on November 5, 2021
I think your primary problem is that you are using non-Unicode string delimiters for Unicode data.
Compare:
SELECT ' "پ*" ';
SELECT N' "پ*" ';
-------^ this N is important
Answered by Aaron Bertrand on November 5, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP