February 17, 2006 at 5:47 pm
I need to do multiple wild card searches in a table with a large varchar column. A web page will send my stored procedure a delimited string of words. I parse out each word into a temp table, and then use a LIKE operator in the WHERE clause to test each word with a separate SELECT statement. I have worked out a method, but I can't test it very well because I don't have much data yet, just a little bit that I was able to poke in myself. Is this an OK method, or can someone share a better way? We do not have full text indexing turned on for this server.
Here is the description of the table to be searched (about 10,000 rows):
CREATE TABLE Course (
CourseID int IDENTITY (1, 1) NOT NULL ,
SAPObjectID int NOT NULL ,
LongTitle varchar (50) NOT NULL ,
ShortTitle char (10) NOT NULL ,
MinStudents smallint NOT NULL ,
MaxStudents smallint NOT NULL ,
OptimumStudents smallint NOT NULL ,
Author varchar (50) NOT NULL ,
Certificate char (10) NULL ,
CEUs char (5) NOT NULL ,
Cost char (10) NULL ,
InstructionLength char (10) NOT NULL ,
OrgUnit char (10) NOT NULL ,
CourseDesc varchar (8000) NOT NULL ,
IsEligEdReimburse bit NOT NULL ,
SelfEnrollment bit NOT NULL ,
DisplayTMSSearch bit NOT NULL ,
HasPrerequisites bit NOT NULL ,
Sponsor varchar (50) NOT NULL ,
SMUDPaid char (10) NOT NULL ,
SubjectAreaID int NOT NULL ,
SupervisorApprovalReqd bit NOT NULL ,
OnRegisterNotifySupervisor bit NULL ,
VendorID int NOT NULL ,
CategoryID int NOT NULL ,
IsActive bit NOT NULL ,
LastUpdated smalldatetime NOT NULL ,
LastUpdatedBy char (7) NOT NULL ,
AllowWaitingList bit NOT NULL ,
AllowInterestList bit NOT NULL ,
AllowSelfCancel bit NOT NULL ,
CompletionPassFail bit NOT NULL ,
CompletionGrade bit NOT NULL ,
CompletionPercent bit NOT NULL ,
ClassEnrollLockDays smallint NOT NULL ,
ActivatedDate smalldatetime NULL ,
ActivatedBy char (7) NULL ,
DeactivatedDate smalldatetime NULL ,
DeactivatedBy char (7) NULL )
----------- here is the proc so far
CREATE PROCEDURE dbo.FindAClass
@CourseDesc varchar(1000)
AS
DECLARE @CourseDescArray TABLE (ElementID smallint, Element varchar(50))
-- this table will be loaded with course and session (if any are scheduled) data
DECLARE @searchresults TABLE (
RowID smallint IDENTITY(1, 1) NOT NULL,
CourseID int NOT NULL,
CourseLongTitle varchar(50) NOT NULL,
CourseDescBlurb varchar(100) NOT NULL,
SessionID int NOT NULL,
SessionLocation varchar(50) NOT NULL,
SessionStartDate smalldatetime,
SessionEndDate smalldatetime
)
SET @CourseDesc = LTRIM(RTRIM(@CourseDesc))
IF ( LEN(@CourseDesc) > 0 )
BEGIN
-- split the Course description words into a table variable
INSERT INTO @CourseDescArray (ElementID, Element)
SELECT ElementID, Element FROM SplitWords(@CourseDesc, '~') ORDER BY ElementID
-- count the words returned
END
-- get Courses that match the course description words
SET @i = 1
WHILE (@i <= @CourseDescCount)
BEGIN -- loop through @CourseDescArray
SELECT @Like = '%' + Element + '%' FROM @CourseDescArray WHERE ElementID = @i
INSERT INTO @searchresults (
CourseID,
CourseLongTitle, -- varchar(50) NOT NULL,
CourseDescBlurb, -- varchar(100) NOT NULL,
SessionID, -- int NOT NULL,
SessionLocation,
SessionStartDate,
SessionEndDate)
SELECT CourseID, LongTitle, SUBSTRING(CourseDesc, 1, 50), 0, '', 19000101, 19000101
FROM dbo.Course
WHERE CourseDesc LIKE @Like
SET @i = @i + 1
END
I would appreciate any input. Thanks.
February 17, 2006 at 5:55 pm
If you're going to execute this sproc for every web request, you might like to move parsing the course results out somewhere else -- they only need to be reparsed when the courses are edited, rather than every time someone executes the search.
February 20, 2006 at 3:21 pm
To avoid the loop you could use the following:
select ....
from Course C
inner join @CourseDescArray CDA
on substring (CDA.element,C.CourseDesc)>0
February 20, 2006 at 6:40 pm
Same script but no loops:
SELECT CourseID, LongTitle, SUBSTRING(CourseDesc, 1, 50), 0, '', 19000101, 19000101
FROM dbo.Course C
INNER JOIN @CourseDescArray A on C.CourseDesc LIKE '%'+A.Element+'%'
GROUP BY CourseID, LongTitle, SUBSTRING(CourseDesc, 1, 50)
_____________
Code for TallyGenerator
February 21, 2006 at 11:19 am
Thanks, that's a much better way than the loop.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply