SQL Server GUID sometimes valid and sometimes not

Question

I'm having a problem with a GUID which I've narrowed down to what looks like a problem with the LIKE operator.

When the following is run on my server

DECLARE @Problem NVARCHAR(1000) = N'58C21BC6-081B-4E57-BFE1-5B11AAC662F1';

DECLARE @GuidPattern NVARCHAR(1000) = 
    REPLICATE('[0-9A-Fa-f]', 8)
    + '-'
    + REPLICATE('[0-9A-Fa-f]', 4)
    + '-'
    + REPLICATE('[0-9A-Fa-f]', 4)
    + '-'
    + REPLICATE('[0-9A-Fa-f]', 4)
    + '-'
    + REPLICATE('[0-9A-Fa-f]', 12);

SELECT
    CASE
        WHEN @Problem LIKE @GuidPattern THEN 1
        ELSE 0
    END AS [FollowsPattern];

the answer is 0, but when I run the exact same code on my local machine the answer is 1.

It looks pretty obvious to me that the string is in fact a valid GUID, so the answer should be 1 in both cases. All the other ways I know about to confirm that the GUID is valid also succeed, both locally and on the server:

SELECT
    CASE
        WHEN CAST(@Problem AS UNIQUEIDENTIFIER) = @Problem THEN 1
        ELSE 0
    END AS [IsGuid1]; -- 1

SELECT
    CASE
        WHEN CONVERT(UNIQUEIDENTIFIER, @Problem) = @Problem THEN 1
        ELSE 0
    END AS [IsGuid2]; -- 1

SELECT
    CASE
        WHEN TRY_CONVERT(UNIQUEIDENTIFIER, @Problem) IS NOT NULL THEN 1
        ELSE 0
    END AS [IsGuid3]; -- 1

The server version is

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) 
    Apr 29 2016 23:23:58 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

and my local installation version is

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
    Feb 20 2014 20:04:26
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

The default collation is the not the same (SQL_Latin1_General_CP1_CI_AS locally and Danish_Norwegian_CI_AS on the server), but I don't think that should matter since I'm dealing with Unicode anyway. Adding an extra COLLATE clause on either machine with the other's collation makes no difference. Update: Not true, the collation is the source of the problem. I only tested different collations in the variable declaration, not in the comparison itself.

Answer 1

I suppose the problem is COLLATION.

Have a look at: http://rextester.com/IRQEFU33639

If you use default collation:

SELECT
    CASE
        WHEN @Problem LIKE @GuidPattern THEN 1
        ELSE 0
    END AS [FollowsPattern];

The result = 1

Instead, if you forces Danish_Norwegian_CI_AS collation:

SELECT
    CASE
        WHEN @Problem collate Danish_Norwegian_CI_AS  LIKE @GuidPattern THEN 1
        ELSE 0
    END AS [FollowsPattern];

It returns 0.

I'd suggest to force the collation to SQL_Latin1_General_CP1_CI_AS or other collation that works well.

SELECT
    CASE
        WHEN @Problem COLLATE SQL_Latin1_General_CP1_CI_AS LIKE @GuidPattern THEN 1
        ELSE 0
    END AS [FollowsPattern];

And just to make it easy maybe using an INLINE User Defined Function.