CODEWITHSUNDEEP
sqlsql-serversql-like
Shushil Bohara
Shushil Bohara

Reputation: 5656

Using `_%` together in `LIKE` is not returning exact data

I can guesss it may be easy to answer type question but I am facing it first time, so any help will be more appreciated.

My Query:

SELECT remarks FROM enroll WHERE remarks LIKE 'REC_%'

OUTPUT:

remarks
REC_59161
Reclassify Hedge

Expected Output is only REC_59161. Yes _ is used for matching any single character but I am just looking for achieving my expected output.

Upvotes: 0

Views: 107

Answers (4)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

There are two issues:

  1. It seems that the column is case insensitive, see Is the LIKE operator case-sensitive with MS SQL server? for details. That's why Rec in Reclassify Hedge fits REC in the Like
  2. _ (as well as %) is a wild card, you should escape _ in the pattern

Query:

  SELECT remarks 
    FROM enroll 
   WHERE remarks LIKE 'REC*_%' ESCAPE '*' /* SQL 92 standard */

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

uses the triadic operator LIKE (or the inverse, NOT LIKE), operating on three character strings and returning a Boolean. LIKE determines whether or not a character string "matches" a given "pattern" (also a character string). The characters '%' (percent) and '_' (underscore) have special meaning when they occur in the pattern. The optional third argument is a character string containing exactly one character, known as the "escape character", for use when a percent or underscore is required in the pattern without its special meaning.

Upvotes: 0

Ullas
Ullas

Reputation: 11556

_ is a wildcard Character. So that you have to escape it using [].

Query

select remarks
from enroll
where remarks like 'REC[_]%';

Upvotes: 3

Ilyes
Ilyes

Reputation: 14928

_ is a wildcard character, Try this :

declare @enroll table (remarks varchar(50));
insert into @enroll values ('REC_59161') , ('Reclassify Hedge');
SELECT remarks FROM @enroll WHERE remarks LIKE 'REC[_]%';

Demo

Upvotes: 1

Dai
Dai

Reputation: 155035

The underscore _ character is actually a special character with the LIKE operator, as are %, [] and ^:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql

In that article, you'll see that an underscore _ matches any single character.

Use the ESCAPE keyword to define an escape character to allow you to escape the pattern-matching characters. We can use ! as the escape character, so:

WHERE remarks LIKE 'REC!_%' ESCAPE '!'

Upvotes: 2

Related Questions