T-SQL Query not returning correct record set

Hagberg Rolf (Bravida) 20 Reputation points
2024-06-12T05:35:27.03+00:00

Query which fails to return '+202AA=532GPG11' :

SELECT DISTINCT PostBet AS [Pos],Benämning AS [Descr] FROM Apparatlista WHERE ((Postbet LIKE '+202A[9AB]=532GPG11')) ORDER BY [Pos]

Returns:

+202A9=532GPG11 TRYCKVAKT

+202AB=532GPG11 TRYCKVAKT


But...

SELECT DISTINCT PostBet AS [Pos],Benämning AS [Descr] FROM Apparatlista WHERE ((Postbet LIKE '+202AA=532GPG11')) ORDER BY [Pos]

Will return:

+202AA=532GPG11 TRYCKVAKT

Is this a bug or what?

SQL Server Management Studio 15.0.18390.0

SQL Server Management Objects (SMO) 16.100.46521.71

Microsoft Analysis Services Client Tools 15.0.19714.0

Microsoft Data Access Components (MDAC) 10.0.19041.2604

Microsoft MSXML 3.0 6.0

Microsoft .NET Framework 4.0.30319.42000

Operating System 10.0.19044

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,317 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 106.5K Reputation points
    2024-06-12T21:57:38.04+00:00

    What collation do you have? Judging from your identifiers, a Finnish_Swedish collation comes closest at hand.

    However, the behaviour you see indicates that you have a Danish collation. In a Danish collation AA = Å.

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 43,161 Reputation points
    2024-06-12T08:07:23.6166667+00:00

    Is this a bug or what?

    No bug in SQL Server, but may in your data/code.

    This example returns the wanted "AA" row as well:

    ;WITH example AS
        (SELECT N'+202A9=532GPG11' AS col UNION ALL
         SELECT N'+202AB=532GPG11' UNION ALL
         SELECT N'+202AA=532GPG11')
    SELECT *
    FROM example AS ex
    WHERE ex.col LIKE N'+202A[9AB]=532GPG11';