Select query with Like matching exact word only?

Question

I like to write select query finding records matching exact "advert" text.

Select * FROM TextTable Where Content Like '%advert%' 

This returns Content with "Advertise is", "This Madverting " or "advert is about". However I want only "advert is about" containing exact word matching row.

Other DB support exact regular expression on like clause. But, tsql doesn't.

So, How can I achieve this?

Thank you,

---

MCSD .NET, SCJP, SCJWD

Answer 1

HI,

Just try this code : 

Select * FROM TextTable Where Content Like ' advert '

Your Second Option Is : FULL TEXT INDEXING 

---

Regards, Ryan Lambatan
Please "Mark as Answer" or "Vote as Helpful"

Answer 2

DECLARE @t TABLE (Content varchar(MAX))

INSERT @t
SELECT  'Advertise is'
UNION ALL SELECT 'This Madverting' 
UNION ALL SELECT 'advert is about'
UNION ALL SELECT 'The last word is advert.'

SELECT *
FROM @t
WHERE ' ' + Content + ' ' LIKE '% advert %'
/* output
Content
advert is about
*/

--OR if you allow puctuations(dot) after the word

SELECT *
FROM @t
WHERE ' ' + Content + ' ' LIKE '% advert[ .]%'
/*output
Content
advert is about
The last word is advert.
*/

Answer 3

hI,

 

Take a look for this link : http://msdn.microsoft.com/en-us/library/ms187787.aspx

---

Regards, Ryan Lambatan
Please "Mark as Answer" or "Vote as Helpful"

Answer 4

1. LIKE '% advert %' doesn't return row starting with advert like 'advert is..'

2. I missed another condition. I also want to search "A people" exact words. Full text search noise words issue have a problem.

---

MCSD .NET, SCJP, SCJWD

Answer 5

1. LIKE '% advert %' doesn't return row starting with advert like 'advert is..'

 

It does. See the test data in my query which returns 'advert is about'. Note that we append spaces at the start and end of Content.

 

2. I missed another condition. I also want to search "A people" exact words. Full text search noise words issue have a problem.

 

Would just changing the "advert" to "A people" not work?

i.e.

WHERE ' ' + Content + ' ' LIKE '% A people %'