Odd behavior of SQL Server LIKE statement when using VARCHAR and NVARCHAR pattern

Question

(Using SQL Server 2019 CU 31)

Why does only the CASE expression against @nvarchar_dash_after_ranges return 'LIKE' in this query? Note that the same comparison against a VARCHAR expression returns 'NOT LIKE'.

USE master;
DECLARE @varchar_dash_at_beginning VARCHAR(100) = '%[^-A-Za-z0-9/]%';
DECLARE @varchar_dash_after_ranges VARCHAR(100) = '%[^A-Za-z0-9-/]%';
DECLARE @nvarchar_dash_at_beginning NVARCHAR(100) = N'%[^-A-Za-z0-9/]%';
DECLARE @nvarchar_dash_after_ranges NVARCHAR(100) = N'%[^A-Za-z0-9-/]%';
DECLARE @str VARCHAR(50) = '-';
SELECT '@varchar_dash_at_beginning' AS expression_name,
       (SELECT CASE
                   WHEN @str LIKE @varchar_dash_at_beginning
                       THEN 'LIKE'
                   ELSE 'NOT LIKE'
               END AS like_eval) AS result
UNION ALL
SELECT '@varchar_dash_after_ranges' AS expression_name,
       (SELECT CASE
                   WHEN @str LIKE @varchar_dash_after_ranges
                       THEN 'LIKE'
                   ELSE 'NOT LIKE'
               END AS like_eval) AS result
UNION ALL
SELECT '@nvarchar_dash_at_beginning' AS expression_name,
       (SELECT CASE
                   WHEN @str LIKE @nvarchar_dash_at_beginning
                       THEN 'LIKE'
                   ELSE 'NOT LIKE'
               END AS like_eval) AS result
UNION ALL
SELECT '@nvarchar_dash_after_ranges' AS expression_name,
       (SELECT CASE
                   WHEN @str LIKE @nvarchar_dash_after_ranges
                       THEN 'LIKE'
                   ELSE 'NOT LIKE'
               END AS like_eval) AS result;

score 2 · Answer 1 · edited Jun 03 '25 at 15:13

Too long for a comment, so I'm putting this here, it is almost certainly the collation you are using for the query.

SQL_Latin1_General_CP1_CI_AS has different rules for char vs nchar strings. You can force the issue by specifying Latin1_General_CI_AS for the collation of the comparison (see below), Latin1_General_CI_AS has the same rules for char and nchar strings.

Link for more details: Revised: Difference between collation SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS

USE [master]
SELECT @@VERSION, collation_name FROM sys.databases WHERE name = DB_NAME()
DECLARE @varchar_dash_at_beginning VARCHAR(100) = '%[^-A-Za-z0-9/]%';
DECLARE @varchar_dash_after_ranges VARCHAR(100) = '%[^A-Za-z0-9-/]%';
DECLARE @nvarchar_dash_at_beginning NVARCHAR(100) = N'%[^-A-Za-z0-9/]%';
DECLARE @nvarchar_dash_after_ranges NVARCHAR(100) = N'%[^A-Za-z0-9-/]%';
DECLARE @str VARCHAR(50) = '-';
SELECT '@varchar_dash_at_beginning' AS expression_name,
       (SELECT CASE
                   WHEN @str COLLATE Latin1_General_CI_AS LIKE @varchar_dash_at_beginning
                       THEN 'LIKE'
                   ELSE 'NOT LIKE'
               END AS like_eval) AS result
UNION ALL
SELECT '@varchar_dash_after_ranges' AS expression_name,
       (SELECT CASE
                   WHEN @str COLLATE Latin1_General_CI_AS LIKE @varchar_dash_after_ranges
                       THEN 'LIKE'
                   ELSE 'NOT LIKE'
               END AS like_eval) AS result
UNION ALL
SELECT '@nvarchar_dash_at_beginning' AS expression_name,
       (SELECT CASE
                   WHEN @str COLLATE Latin1_General_CI_AS LIKE @nvarchar_dash_at_beginning
                       THEN 'LIKE'
                   ELSE 'NOT LIKE'
               END AS like_eval) AS result
UNION ALL
SELECT '@nvarchar_dash_after_ranges' AS expression_name,
       (SELECT CASE
                   WHEN @str COLLATE Latin1_General_CI_AS LIKE @nvarchar_dash_after_ranges
                       THEN 'LIKE'
                   ELSE 'NOT LIKE'
               END AS like_eval) AS result;

Odd behavior of SQL Server LIKE statement when using VARCHAR and NVARCHAR pattern

1 Answers1