CODEWITHSUNDEEP
sqlsql-serverssmswildcard
Sonali Agrawal
Sonali Agrawal

Reputation: 353

SQL: Return records containing a word where the last letter is anything except K

Suppose I have a table containing a column by the name "Query". I have to display the records where the string in this column has used noloc instead of nolock. Note that noloc can be followed/preceded by ) or space etc. I just need all records that have anything before and after noloc except nolock. Is there a way to do it in SQL?

I tried:

select * from table1
where Query LIKE '%noloc%'

but this includes queries containing nolock. I tried variations of the above like putting space before and/or after % but none of them fills all the criteria.

Upvotes: 1

Views: 69

Answers (2)

George Menoutis
George Menoutis

Reputation: 7250

You want anything + noloc + any one char but k + anything. Here:

select * from table1
where Query LIKE '%noloc[^k]%'

Upvotes: 3

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

You can use both conditions in the where clause

select * from table1
where Query LIKE '%noloc%' and Query NOT LIKE '%nolock%'

Upvotes: 5

Related Questions