Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dennis Falls
Starting Member
41 Posts |
 Posted - 2003-12-09 : 19:40:51
|
| In my table visit, I need to search the field notes for values in table syndromes field phrase varchar(30). The notes field is varchar(255) and the value from phrase can be anywhere in the notes field. For example, a phrase in syndrome is 'fever' and the notes field might say something like 'patient has a high fever but not flu'.There are 14,000 phrases and 500,000 records in notes.So, how can I do this? |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-12-09 : 19:49:49
|
| You can do this with LIKESELECT * FROM syndromes WHERE notes LIKE '%fever%'Or in a stored proc it would look like this :SELECT * FROM syndromes WHERE notes LIKE '%' + @phrase + '%'Damian |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2003-12-09 : 19:56:47
|
| Or if you're a sucker for punishment, you can do a variation of this.select so1.name, so2.namefrom sysobjects so1 inner join syscomments sc1 ON sc1.text like '%' + so1.name + '%' inner join sysobjects so2 ON sc1.id = so1.idwhere so2.type = 'P'order by so1.nameMeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-12-09 : 20:01:22
|
| Dennis,Look at Full Text Indexing...DavidM"SQL-3 is an abomination.." |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-12-09 : 21:27:12
|
| Another option is this. I've used this when I did not have access to Full text and it works great!http://www.sqlteam.com/item.asp?ItemID=5857Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2003-12-11 : 17:34:13
|
| I might wanna try a tally table technique if I'm ever allowed to upgrade things. Things work righ tnow so probably not but there are a few 10-15 second searches occuring, but it probably won't be until things start timing out when I'll be allowed to upgrades because of all the other things to do.Fulltext searches all records without regard to external filtering, then it has the rank them all if the keywords are very general!. So if the category restriction limits it to 10 rows on the left side of the join to the full text search but the keywords would match 50,000 records, then it searches 200,000 records and ranks 50,000 even though only 10 would make it through the full text join (some of the 10-15 second seraches are like this)! Telling full text to Limit results don't work becuase it limits without regard to the rest of the query.****************************************Check out my pictures at http://www.pbase.com/GreatIncaCheck out my company's site!http://www.jobing.com (Market picker for first visit)http://phoenix.jobing.com (corporate home market if you don't live in a covered market) |
|
|
|
|
|
|
|
|
Search Question