How can I escape a caret, tilde and curly braces in T-SQL?-CodePudding

I'd like to find special characters in SQL like this tutorial, but I'd like to expand the character set.

Say I have a table:

CREATE TABLE SpecialCharacter (
    SampleString varchar(255)
);

INSERT INTO SpecialCharacter (SampleString) VALUES ('   !"#$%&''()* ,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~');
INSERT INTO SpecialCharacter (SampleString) VALUES (' !"#$%&''()* ,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_abcdefghijklmnopqrstuvwxyz{}~');
INSERT INTO SpecialCharacter (SampleString) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz');
INSERT INTO SpecialCharacter (SampleString) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz\u001a');
INSERT INTO SpecialCharacter (SampleString) VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyzģ');
INSERT INTO SpecialCharacter (SampleString) VALUES ('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz');
INSERT INTO SpecialCharacter (SampleString) VALUES ('-');
INSERT INTO SpecialCharacter (SampleString) VALUES ('c-r');
INSERT INTO SpecialCharacter (SampleString) VALUES ('_noice');
INSERT INTO SpecialCharacter (SampleString) VALUES ('_noice^^');

And I have the below query:

select * from SpecialCharacter WHERE SampleString LIKE '%[^ !"#$\%&''()* ,-./0-9A-Za-z:;<=>@\[\\\]\_]%' ESCAPE '\' AND SampleString LIKE '%[^{}~]%' ESCAPE '\'

I've managed to figure out how to find all the characters except for ^. The closest I got for ^ was something like the below:

select * from SpecialCharacter WHERE SampleString LIKE '%[\^]%' ESCAPE '\'

This works when I want to find data in column containing ^. However, I'd like to find data in column that contains something that's not in the list of acceptable characters.

~, { and } on its own works, but does not work when I put it into the LIKE statement with everything else, which is why I separated them. But if I can put them in the same statement that'd be great, too.

What am I missing? I can't use CONTAINS or FREETEXT, or variables etc, the query should be as simple as possible.

CodePudding user response：

Don't escape it.

As you already found out, if you want to find the caret, ie you are interested in it for its "character", you have to escape it.

WHERE SampleString LIKE '%[\^]%' ESCAPE '\'

When you are trying to fing strings that do not contain something, ie use the caret for its "function", you have to not escape it:

where SampleString LIKE '%[^~{}]%' ESCAPE '\'

You could actually use both escaping and not escaping in the following example, where you would get strings that do not contain the caret:

where SampleString LIKE '%[^\^]%' ESCAPE '\'

CodePudding user response：

Turns out I was missing the question mark; the query itself with the special characters is alright.

select * from SpecialCharacter WHERE SampleString LIKE '%[^ !"#$\%&''()* ,-./0-9A-Za-z:;<=>@?\[\\\]\_\^{}~]%' ESCAPE '\'