MSSQL Query help - Finding an exact word in a field

Are there a fixed set of delimiters in use in your "title" field?   If so then you could look for:   (Title = 'CAP'   OR Title like 'CAP %'   OR Title like '% CAP %'   OR Title like '% CAP') We are trying to tease out the following: SELECT     ProjNum, ClientName, tblProj.Title, StartDate FROM         tblProj WHERE     (tblProj.Title LIKE '%Cost Plan%') OR            (tblProj.Title LIKE '%Cost Allocation Plan%') OR             (tblProj.Title LIKE '%CAP%') The problem is that "OR   (tblProj.Title LIKE '%CAP%')" pulls out everything with CAP in it, like captital, captain etc. (as it should). What we want is to get everything with just the word CAP in it and not word that contains CAP. I know it can be done using Full-Text Search but we don't have it enabled on the DB and installing it is not going to happen any time soon. And this is some what time urgent. Does anyone know how to do that with out enabling Full-Text Search on SQL Server? TIA G "We learn something every day, and lots of times it's that what we learned the day before was wrong." - Bill Vaughan

I know you can use square brackets and the carrot symbol to exclude characters. LIKE 'CAP[^T]%' Will give you everything that does not begin with CAPT So I _think_ you can do this.  Try it and let me know. LIKE '%[^abcdefghijklmnopqrstuvwxyz]CAP[^abcdefghijklmnopqrstuvwxyz]%' You are probably going to have to look at the records as you are outputting them and toss out the bad ones so they don't display. Are you sure you cant turn FTS on?  It is sooo much faster than LIKE. ----- Excess quoted text cut - see Original Post for more -----

I re-read my post and I don't make any sense.   I think I was typing faster than my brain was working.  :) LIKE 'CAP[^T]%' Will not return records with CAPT at the beginning. So LIKE '%[^abcdefghijklmnopqrstuvwxyz]CAP[^abcdefghijklmnopqrstuvwxyz]%' Will return records with CAP or 8CAP9, but won't return records with CAPTAIN or CAPITAL. At least I think it will.  Try it out.  I am not in front of a MS SQL server. ----- Excess quoted text cut - see Original Post for more -----

I am full of incomplete thoughts. I _think_ you can abbreviate with a-z and 1-9 instead of typing out the characters. ----- Excess quoted text cut - see Original Post for more -----

Chad, we got that to work with some tweaks. It was skipping stuff where CAP as the first word or last word. But yeah that did the trick. Thanx to everyone that helped! You helped save the day. G$ On Wed, May 21, 2008 at 2:29 PM, Chad Gray <cgray@careyweb.com> wrote: ----- Excess quoted text cut - see Original Post for more -----

In MySQL... '%CAP %' works. We are trying to tease out the following: SELECT     ProjNum, ClientName, tblProj.Title, StartDate FROM         tblProj WHERE     (tblProj.Title LIKE '%Cost Plan%') OR            (tblProj.Title LIKE '%Cost Allocation Plan%') OR             (tblProj.Title LIKE '%CAP%') The problem is that "OR   (tblProj.Title LIKE '%CAP%')" pulls out everything with CAP in it, like captital, captain etc. (as it should). What we want is to get everything with just the word CAP in it and not word that contains CAP. I know it can be done using Full-Text Search but we don't have it enabled on the DB and installing it is not going to happen any time soon. And this is some what time urgent. Does anyone know how to do that with out enabling Full-Text Search on SQL Server? TIA G "We learn something every day, and lots of times it's that what we learned the day before was wrong." - Bill Vaughan