Why doesn't Azure SQL think that an underscore comes between a space and "ZZZZZ"?-CodePudding

I just found a what I think is a bug in Azure SQL:

The query below, shows results:

Select * from table where '_' BETWEEN ' ' AND 'ZZZZZZZ',

but in fact, in my understanding and in regular SQL Server or Oracle, this query doesn't show any results.

Any thoughts here?

CodePudding user response：

Trailing spaces are disregarded in = and between comparisons. So ' ' is equivalent to '', which the lowest non-null value in the varchar sort order for the current (and I think every) collation. This is the same on SQL Server and Azure SQL Database.

In currently in all non-binary collations 'Z' > '_' and in all binary collations 'Z' < '_', but ' ' is always lower.

You can run the comparison in all collations with dynamic SQL. EG

declare c cursor local for select name from fn_helpcollations()
declare @collation sysname

declare @collations table(collation sysname, is_Z_less_than_underscore bit)

open c
fetch next from c into @collation
while @@FETCH_STATUS = 0
begin
  declare @sql nvarchar(max) = 'select '''   @collation   ''', case when ''Z'' < ''_'' collate '   @collation   ' then 1 else 0 end'
  begin try
    insert into @collations
    exec (@sql)
  end try
  begin catch 
    print error_message()
  end catch
fetch next from c into @collation
end
close c
deallocate c

select *
from @collations
order by 2,1

CodePudding user response：

It also returns results in MS SQL. Since '_' in fact seems to be between ' ' and 'ZZZZZZZ' for certain collations, it is actually the same as

Select * from table where 1=1