CODEWITHSUNDEEP
sql-servert-sql
Joseph Morales
Joseph Morales

Reputation: 3

Select Rows with a Substring that Lacks a Certain Prefix

I have a table with a nvarchar column. I want to select rows where that column includes a certain word, but only if it is not preceded by another word. Example data:

DECLARE @T AS TABLE
(
    Phrase nvarchar(50)
)

INSERT INTO @T VALUES (N'Jeffie likes big cookies.'),
    (N'Good cookies can be made with chocolate.'),
    (N'Maggie likes big cookies and little cookies.')

The query should return rows where 'cookies' appears if it is not immediately preceded by 'big'. For the above data, the query should return the first and third rows. The third row is the problem, because 'cookies' appears twice and is only preceded by 'big' once. The closest I have gotten is the following query, but it isn't quite right:

SELECT * FROM @T 
WHERE Phrase LIKE N'%cookies%'
AND Phrase NOT LIKE N'%big cookies%'

The problem with this query is that it returns only the first row, and I want it to return both the first row and the third row. Thanks in advance for any suggestions!

Upvotes: 0

Views: 131

Answers (3)

Pawan Kumar
Pawan Kumar

Reputation: 2021

Please try this simple solution - 

DECLARE @T AS TABLE ( Phrase nvarchar(50) )

INSERT INTO @T VALUES 
  (N'Jeffie likes big cookies.')
, (N'Good cookies can be made with chocolate.')
, (N'Maggie likes big cookies and little cookies.'),
(N'big I am here cookies and little cookies.'),
(N'anything here'),
(N'Maggie likes big cakes and little cookies'),
(N'big cookies big cookies')

SELECT * FROM @T where CHARINDEX ('cookies' , REPLACE(Phrase,'big cookies','xxx'))  >0

OUTPUT 

Phrase
--------------------------------------------------
Good cookies can be made with chocolate.
Maggie likes big cookies and little cookies.
big I am here cookies and little cookies.
Maggie likes big cakes and little cookies

(4 rows affected)

Upvotes: 1

Anil Soman
Anil Soman

Reputation: 2467

Try this:

SELECT * FROM @T 
WHERE Phrase LIKE N'%big cookies%'
AND charindex('cookies',Phrase) > 0

The order of execution of WHERE clause will check the first condition with 'big cookies' and then it will solve the purpose because of AND.

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

try This Approach

DECLARE @T AS TABLE
(
    Phrase nvarchar(50)
)

INSERT INTO @T VALUES 
(N'Jeffie likes big cookies.'),
(N'Good cookies can be made with chocolate.'),
(N'Maggie likes big cookies and little cookies.')


SELECT 
    *
    FROM @T
       WHERE (LEN(Phrase)-LEN(REPLACE(Phrase,'Big Cookies','')))/LEN('Big Cookies') 
       <> (LEN(Phrase)-LEN(REPLACE(Phrase,'cookies','')))/LEN('cookies')

My Result

enter image description here

Upvotes: 2

Related Questions