SQL Server how to escape plus and minus sign?

Question

Declare @email varchar(max) = '+'
If @email LIKE '%[!#$%&''*+-/=?^_`{|}~]%'
PRINT @email

All special characters work fine except the + and - sign.

I tried to add \ before the plus sign in the If statement, but it doesn't work.

Declare @email varchar(max) = '+'
If @email LIKE '%[!#$%&''*\+-/=?^_`{|}~]%'
PRINT @email

If I add \ before the plus sign in both the Declare and If statement, then it prints \+

Declare @email varchar(max) = '\+'
If @email LIKE '%[!#$%&''*\+-/=?^_`{|}~]%'
PRINT @email

How can I escape the + or - sign?

Answer 1

The issue here is the order of the values in the set that you are trying to match.

Because - is between two other characters +-/ is interpreted as a range (>= '+' and <= '/').

This range matches 5 varchar characters in my default collation. +, ,, -, ., /.

If you are on a collation where / sorts before + then the range will match nothing. Not even + or /.

You don't need to escape anything.

Just put the - as the first character in the set of values that you are trying to match as in the example here

Symbol	Meaning
LIKE '[a-cdf]'	a, b, c, d, or f
LIKE '[-acdf]'	-, a, c, d, or f

Demo

WITH CHARS AS
(
SELECT TOP 255 CHAR(ROW_NUMBER() OVER (ORDER BY @@SPID)) AS C
FROM sys.all_columns
)
SELECT *
FROM CHARS
WHERE C LIKE '[-!#$%&''*+/=?^_`{|}~]'

You can also ESCAPE if you prefer though

Escape characters can be used within the double bracket characters ([ ]), including to escape a caret (^), hyphen (-), or right bracket (]).