SQL Server string concatenation returns blank

Question

Please help me with this challenge.

This code returns a blank result and I can't figure out the reason.

select Vendor_Name + ' || ' + cast(cnt as varchar(12)) as Vendor_Count
from (select top (1) Vendor_Name, count(Vendor_Name) as cnt 
      from dbo.Vendors nolock 
      group by Vendor_Name 
      having count(Vendor_Name)>1 
      order by 2 desc) x 

But...The inner query returns the Vendor_Name blank, and cnt 63420

select top (1) 
    Vendor_Name, count(Vendor_Name) as cnt 
from 
    dbo.Vendors nolock 
group by 
    Vendor_Name 
having 
    count(Vendor_Name) > 1 
order by 
    2 desc

Here is the the result of the inner query without the top (1). As you can see, the blank/empty/null Vendor_Name occurs most frequently.

I check the length of Vendor_name using len() function, and it returns 12. But when i copy the result from the inner query above to an excel sheet, and use the len() function in Excel - excel shows 0. The declared length for Vendor_name in the SQL Server table is nvarchar(50).

I tried isnull(Vendor_Name,'') and COALESCE(Vendor_Name,'') but that didn't make a difference.

I re-arranged the query and got non-blank result, it is interesting that this works but my original query doesn't.

select ' || ' + cast(cnt as varchar(12)) + ' ' + Vendor_Name  
from (select top (1) Vendor_Name, count(Vendor_Name) as cnt 
      from dbo.Vendors nolock 
      group by Vendor_Name 
      having count(Vendor_Name)>1 
      order by 2 desc) x 

But this result is not what i wanted.

Did i miss something?

Thank you!

ps. I have tried to replicate the data to to post it here but without success.

Answer 1

Am suspecting Vendor_Name might start with a NUL ('\0') character indicating end-of-string. This would make it seem to have no content and explain the difference in concatenation order.

To test this, please try REPLACE(Vendor_Name, CHAR(0), ' ') in your outer query