How '^' or '! ' works in SQL server with like operator and wild card(%)

Question

0.00/5 (No votes)

See more:

Hi,
Greetings !!
Today someone asked me to solve a query to find all account name which does not contains 'A' anywhere in the column 'acname' using Like opertor and not to use 'Not Like'. So, I thought we can use '^' or '!' to solve the query but it did not work.

I tried this query
select * from acmast where acname like '%[!a]%'

but fail to resolve this query using '!'

So, I need to know what am I missing

Best Regards,
Sukhen Dass

What I have tried:

select * from acmast where acname like '%[!a]%'

Posted 13-Aug-18 16:02pm

sukhen dass

Updated 15-Aug-18 1:50am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Answer 1 · 2018-08-13T17:46:00

Solution 1

It's really easy to figure out if you read the documentation on LIKE:

LIKE (Transact-SQL) | Microsoft Docs[^]

Posted 13-Aug-18 17:46pm

Dave Kreskowiak

Comments

sukhen dass 14-Aug-18 1:14am

Have read this docs many time but did not find solution as nowhere is given uses of '%[!anyCharacter]%'. only given is either from first not like('[!anyCharacter]%') or from last not like('%[!anyCharacter]') but not for full search

Dave Kreskowiak 14-Aug-18 9:27am

What you missed was reading the documentation and performing tests to further your understanding. Experiment with WHERE clauses. Figure out exactly how these things work and what their limits are.

"What you tried" says you tried only a single query. It also says you only tried with a "!" character in the query. Reading the documentation, that character isn't even mentioned as a possibility that does anything useful.

"!" isn't even used as a standalone operator of any kind. Google for "TSQL operators" to find out.

This is the world of doing thorough research and it's a HUGE part of development work.

Oh, and to directly answer your question, no it's not possible to use ^ in a LIKE expression to "not find a character" anywhere in a string.

CHill60 · Answer 2 · 2018-08-13T23:37:00

The point Dave is making is that you do not use ! or ^ to indicate "not" in SQL, so that the thing you are "missing" is understanding the syntax of the tool you are trying to use.

There are various techniques that you can use to avoid using NOT LIKE (or NOT IN) here is one example:

SQL

create table demo ([data] varchar(255))
insert into demo ([data]) values
('words without the offending letter'),
('sentence that contains the letter a')

select *
from demo
where patindex('%a%', [data]) = 0

This returns

words without the offending letter

Documentation PATINDEX (Transact-SQL) | Microsoft Docs[^]

Alternatives can be found via Google[^]