SQL Server: why there are no rows?

Question

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 the following 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 the following results:

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

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

Answer 1

Perhaps the first character in f_firm_name is a space.

So try this -

 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 

Answer 2

Most likely, database sorting does not support characters in a string literal by default, and they are lost.

In my default setting, SELECT ' ' returns ??????? ??? ??????? ???

If the nvarchar column is used LIKE N' %'

If varchar is used, use

 LIKE CAST(N' %' COLLATE SQL_Latin1_General_Cp1251_CI_AS AS VARCHAR(50)) 

since it prevents implicit casting and is transferable.