HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Record not getting filtered using like clause

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
gettinglikerecordusingnotclausefiltered

Problem

Using below query to filter records:

select * from SearchRecord where searchkeyword1  like  '[%'


Tried this also

select * from SearchRecord where searchkeyword1  like  '%[%'


Records in table contain these values (as per below i need last 2 records, why this simple like won't filter records)

"Affiliate" means
"Agent's Rate of Exchange" means
["Aggregate Total Commitments" means
["Agreed Security Principles" means

Solution

Your search pattern contains a wildcard character [,
you should enclose it in brackets or use ESCAPE character like this:

declare @SearchRecord table (searchkeyword1 varchar(100));
insert into @SearchRecord values
('"Affiliate" means'),
('"Agent''''s Rate of Exchange" means'),
('["Aggregate Total Commitments" means'),
('["Agreed Security Principles" means');

select * 
from @SearchRecord 
where searchkeyword1  like  '![%' ESCAPE '!';

select * 
from @SearchRecord 
where searchkeyword1  like  '[[]%';


All this is described here:
LIKE (Transact-SQL)

Code Snippets

declare @SearchRecord table (searchkeyword1 varchar(100));
insert into @SearchRecord values
('"Affiliate" means'),
('"Agent''''s Rate of Exchange" means'),
('["Aggregate Total Commitments" means'),
('["Agreed Security Principles" means');

select * 
from @SearchRecord 
where searchkeyword1  like  '![%' ESCAPE '!';

select * 
from @SearchRecord 
where searchkeyword1  like  '[[]%';

Context

StackExchange Database Administrators Q#221240, answer score: 12

Revisions (0)

No revisions yet.