SQL Server：为什么没有找到行？

When I use this query:

当我使用此查询时:

SELECT TOP 20 
     f.name as f_firm_name 
FROM Firm f 
WHERE f.id_city = '73041' COLLATE SQL_Latin1_General_Cp1251_CI_AS 
ORDER BY f.name ASC

I get these results:

我得到这些结果:

f_firm_name
--------------------------------
 SKY  LINE STUDIO
 АНТИКВАРНЫЙ САЛОН
 БИЗОН УЛЬЯНОВСК
 ВЕРТЕКС ЗАО
 ВОЗРОЖДЕНИЕ+
 ВОЛГАСПЕЦТЕХНОЛОГИИ
 ГП СЕРВИС
 Данилов А.Б.ИП
 ИНИКОМ
 ИП МАЛАШИН В.Б.
 ИП СУЛАГАЕВ АНДРЕЙ

(20 row(s) affected)

But if I use this query:

但是如果我使用这个查询:

SELECT TOP 20 
     f.name as f_firm_name 
FROM Firm f 
WHERE f.id_city='73041'
  AND f.name LIKE 'ВЕРТЕКС ЗАО%' COLLATE SQL_Latin1_General_Cp1251_CI_AS 
ORDER BY f.name ASC

I get these results:

我得到这些结果:

f_firm_name
-----------------
(0 row(s) affected)

Why am I getting 0 rows if in the first query I get f.name and use that result to search in the second query?

为什么我得到0行,如果在第一个查询中我得到f.name并使用该结果在第二个查询中搜索?

2 个解决方案

#1

Possible, the first character in f_firm_name - is a space.

可能,f_firm_name中的第一个字符 - 是一个空格。

So try this one -

试试这个 -

SELECT TOP 20 f_firm_name = f.name  
FROM dbo.Firm f 
WHERE f.id_city = '73041'
    AND LTRIM(f.name) LIKE 'ВЕРТЕКС ЗАО%' --<--
        COLLATE SQL_Latin1_General_Cp1251_CI_AS 
ORDER BY f.name

#2

Probably your database's default collation does not support the characters in the string literal and they are being lost.

可能您的数据库的默认排序规则不支持字符串文字中的字符,并且它们正在丢失。

Under my default collation SELECT 'ВЕРТЕКС ЗАО' returns ??????? ???

在我的默认排序规则下,SELECT'ВЕРТЕКСЗАО'返回??????? ???

If the column is nvarchar use LIKE N'ВЕРТЕКС ЗАО%'

如果列是nvarchar,请使用LIKEN'ВЕРТЕКСЗАО%'

If it is varchar use

如果是varchar使用

LIKE CAST(N'ВЕРТЕКС ЗАО%' COLLATE SQL_Latin1_General_Cp1251_CI_AS AS VARCHAR(50))

as this prevents an implicit cast and is sargable.

因为这可以防止隐式转换并且是可以攻击的。

#1