SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.SubCategories
(
  FullDescription ntext COLLATE Cyrillic_General_CS_AS
);

INSERT dbo.SubCategories(FullDescription) VALUES
  (N'no match'), (N'match тест for sure');
2 rows affected

SELECT FullDescription
  FROM dbo.SubCategories
  WHERE FullDescription LIKE '%тест%';


FullDescription


SELECT FullDescription
  FROM dbo.SubCategories
  WHERE FullDescription LIKE N'%тест%';


FullDescription


match тест for sure