Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
MSSQL Query help - Finding an exact word in a field
Author: Gerald Guido
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56485#305841
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$
----- Excess quoted text cut - see Original Post for more -----
Author: Chad Gray
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56485#305840
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 -----
Author: Chad Gray
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56485#305839
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 -----
Author: Jason Durham
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56485#305837
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
Author: Chad Gray
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56485#305836
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 -----
Author: Gaulin, Mark
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:56485#305835
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
