CODEWITHSUNDEEP
sql-serversql-likesql-server-2016
Murtaza Mandvi
Murtaza Mandvi

Reputation: 10998

TSQL Like and Not like usage

I have links data as follows :

Data
www.example.com/
www.example.com/someotherpath/
www.example.com/someotherpath/included
www.example.com/someexclusivepath/
www.example.com/someexclusivepath/AnyPathHereShouldBeExcluded/
www.example.com/someexclusivepath/AnyPathHereShouldBeExcluded

I am trying to write a Like condition which should just give me 4 results from above :

www.example.com/
www.example.com/someotherpath/
www.example.com/someotherpath/included
www.example.com/someexclusivepath/

If I do a Like and not like : 

%www.example.com/%' AND Not like '%www.example.com/someexclusivepath/%'

This will exclude some links from above as well and the result would be :

www.example.com/
www.example.com/someotherpath/
www.example.com/someotherpath/included

How do I get the expected result?

UPDATE :

Example :

DECLARE @tbl TABLE (link varchar(200))
INSERT INTO @tbl VALUES
('www.example.com/'),
('www.example.com/someotherpath/'),
('www.example.com/someotherpath/included'),
('www.example.com/someexclusivepath/'),('www.example.com/someexclusivepath/AnyPathHereShouldBeExcluded/'),
('www.example.com/someexclusivepath/AnyPathHereShouldBeExcluded')

SELECT * FROM @tbl

SELECT * FROM @tbl
WHERE link like '%www.example.com/%' AND Link Not like '%www.example.com/someexclusivepath/%'

Upvotes: 0

Views: 454

Answers (2)

TheEsnSiavashi
TheEsnSiavashi

Reputation: 1255

Try this:

DECLARE @tbl TABLE (link varchar(200))
INSERT INTO @tbl VALUES
('www.example.com/'),
('www.example.com/someotherpath/'),
('www.example.com/someexclusivepath/'), 
('www.example.com/someotherpath/included'),    
('www.example.com/someexclusivepath/AnyPathHereShouldBeExcluded/'),
('www.example.com/someexclusivepath/AnyPathHereShouldBeExcluded')

SELECT * FROM @tbl
     WHERE (link like '%/%' or link like '%/%/') 
     and (link not like '%/%/[a-z]%' or link like '%/%/included')

See it in action here.

Upvotes: 1

Rooben
Rooben

Reputation: 98

If they always start the same, and they end with a '/' symbol, you could do the following:

DECLARE @tbl TABLE (link varchar(200))
INSERT INTO @tbl VALUES('www.example.com/'),('www.example.com/someotherpath/'),('www.example.com/someexclusivepath/'),('www.example.com/someexclusivepath/AnyPathHereShouldBeExcluded/')
SELECT * FROM @tbl

SELECT * FROM @tbl
WHERE Link Not like 'www.example.com/someexclusivepath/%/'

Upvotes: 0

Related Questions