May 19, 2009 at 8:25 am
For simplicity's sake let's say I have 3 tables:
1. People
--- personId (int)
--- firstName (varchar(50))
--- lastName (varchar(50))
2. Content
--- contentId (int)
--- title (varchar(250))
--- contents (text)
3. People_Content
--- personId
--- contentId
What would be the best way of searching the "contents" field of a single record in the Content table for all matching names (first + last) from the People table?
I need to insert all matches into People_Content.
May 19, 2009 at 8:31 am
The only way I can think of is using LIKE operator
SELECT *
FROM People p
INNER JOIN [Content] c ON c.contents LIKE '%' + firstName + lastName + '%'
--Ramesh
May 19, 2009 at 8:41 am
The contents field is a text field, thus can not be searched using LIKE.
I can split the content up and put it in a temp table for searching before I insert it, but that seemed a bit clunky.
I have tried that using:
select personId from People where exists(
select 1 as expr1 from Content where contents like '%'+firstName+' '+lastName+'%' and contentId=
)
(where is the id of the content i need to search and the contents field was a varchar(8000))
but this seemed to take a little longer than I'd like (about 5 seconds on first run).
I need this to run as soon as the content is published, and don't want the user clicking a "submit" button and having it hang while the database is crunching.
May 19, 2009 at 8:46 am
if you are limiting to a single Id, it should be fast...you could convert the textfield to varchar(max) for the query: if it's going to scan all rows for a match, it'll be slow
SELECT *
FROM People p
INNER JOIN [Content] c ON p.id = c.id
WHERE CONVERT(varchar(max)mc.contents) LIKE '%' + firstName + lastName + '%'
and p.id = 42
Lowell
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply