CODEWITHSUNDEEP
sqlsql-servert-sql
transport
transport

Reputation: 61

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

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.

Upvotes: 2

Views: 1875

Answers (2)

George Menoutis
George Menoutis

Reputation: 7240

Don't escape it.

As you already found out, if you want to find the caret, i.e. you are interested in it as a character, you have to escape it.

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

When you are trying to find strings that do not contain something, i.e. 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 contains something else other than the caret:

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

Upvotes: 3

transport
transport

Reputation: 61

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 '\'

Upvotes: 0

Related Questions