May 20, 2010 at 7:57 am
Hi all,
I hope this is not a stupud question but I can't seem to find a solution.
I have a search textbox on a website. The employee needs to be able to put in multiple words to search on. ex. "chair wood".
I would like to create a SP that takes the string and finds all records where the product description is either chair or wood. I can build a SQL string and send it to the database (Select * from tablename where description like %'chair'% or description like %'wood'% ) but I was hoping that there was a way to do this using a SP.
Can anyone help me in the syntax?
In the future I would like to be able to let the employees put in and / or between words and be able to parse the search string appropriatly for the correct answer.
Thanks!
Eric
May 20, 2010 at 8:07 am
If I were you, I would check the home page of SQL Server Central, there is a headlight talking about how to get good performance, and good protection when using Dynamic SQL. It should serve you good, I just finished reading it. If you have any questions regarding it, you can post them.
Hope this helps,
Cheers,
J-F
May 20, 2010 at 8:36 am
J-T,
Interesting article, thanks for pointing me to it. I learned something
However it doesn't really answer my question. Both Chair and Wood are being pulled from the same field. Now if I wanted to say there would only be a max of say 5 words then I could write the parameter list to have searchName1, searchName2, etc. and get the desired results.
That might work since I really don't want them to put in too many terms. Maybe increasing the number to 8 or so.
But I am still looking for the way (if possible) to simply pass the string and have the SP parse it correctly.
Thanks again,
Eric
May 20, 2010 at 9:19 am
If it's splitting the string you need to do, then you can use this piece of code, to split it, and add it to your where statement dynamically.
DECLARE @MfrCodes VARCHAR(8000)
SELECT @MfrCodes = '1DEF,ABS,ACI,ACL,AEA' ;
SELECT @MfrCodes = ',' + @MfrCodes + ',' ;
WITH cteTally
AS ( SELECT ROW_NUMBER() OVER ( ORDER BY o.id ) AS N
FROM master.sys.sysobjects o
CROSS JOIN master.sys.sysobjects o2
),
cteSplit
AS ( SELECT SUBSTRING(@MfrCodes, N + 1, CHARINDEX(',', @MfrCodes, N + 1) - N - 1) AS VALUE
FROM cteTally
WHERE N < LEN(@MfrCodes)
AND SUBSTRING(@MfrCodes, N, 1) = ','
)
SELECT *
FROM cteSplit
This cte will help you build your string for the dynamic SQL.
Cheers,
J-F
May 20, 2010 at 9:33 am
You might want to consider looking in to the full-text search; see this article for more details: http://msdn.microsoft.com/en-us/library/ms142571.aspx. This page talks about the exact nature of full-text search queries: http://msdn.microsoft.com/en-us/library/ms142583.aspx
May 22, 2010 at 6:11 am
Thanks All,
I have ben pulled in a different direction but will look at this in a few days.
Thnaks again!
Eric
June 7, 2010 at 12:12 pm
Hi All,
Well I finally decided to look at my search problem again. A few days ago I saw an article on this site called "A Google-like Full Text Search" and thought this would be perfect.
Although it is an excellent article and process I have a couple of questions/concerns.
Is full text searching good for tables that change often? By often I mean a 200,000 row table with 1000 - 2000 changes a day. Changes may not be in the text search fields as well.
One thing that happened was that the search found some data entry errors in my test data. I updated the fields in the table directly but the same records show up even though they no longer match the criteria.
This caused me to look for and find the "change tracking" options. I found a three different ways (Scheduled, On demand, and Background). I assume that I should use Background but am concerned with the overhead. I was expecting to find these options somewhere in the Managament Studio (table or database) and have not. Is there a place to set this parameter in Management Studio?
I am also planning to write scripts (actually a person here on this site showed me examples) to backup the tables. Will I need to do anything different with my backup scripts? or simply reindex when and if I need to restore the data?
Thanks all!!!
Eric
June 7, 2010 at 12:54 pm
I think that full text has a good possibility here for you.
To answer your original question, use the DelimitedSplit function to return the words into a table.
IF OBJECT_ID('dbo.DelimitedSplit8K') IS NOT NULL DROP FUNCTION dbo.DelimitedSplit8K
GO
CREATE FUNCTION dbo.DelimitedSplit8K
/***************************************************************************************************
Purpose:
Split a given string at a given delimiter and return a list of the split elements (items).
Usage Example:
SELECT *
FROM dbo.DelimitedSplit8K(@StringToSplit, @Delimiter)
Returns:
iTVF containing the following:
ItemNumber = Element position of Item as a BIGINT (not converted to INT to eliminate a CAST)
Item = Element value as a VARCHAR(8000)
Notes:
1. Optimized for VARCHAR(8000) or less.
2. Optimized for single character delimiter.
3. Optimized for use with CROSS APPLY.
4. Does not "trim" elements just in case leading or trailing blanks are intended.
5. cteTally concept originally by Iztek Ben Gan and "decimalized" by Lynn Pettis (and others) for a
bit of extra speed and finally redacted by Jeff Moden for a different slant on readability and
compactness.
6. If you don't know how a Tally table can be used to replace loops, please see the following...
http://www.sqlservercentral.com/articles/T-SQL/62867/
Revision History:
Rev 00 - 20 Jan 2010 - Jeff Moden
- Base 10 redaction for CTE.
Rev 01 - 08 Mar 2010 - Jeff Moden
- Changed UNION ALL to UNPIVOT for bit of extra speed.
Rev 02 - 13 Mar 2010 - Jeff Moden
- Removed one additional concatenation and one subtraction from the SUBSTRING in the
SELECT List.
***************************************************************************************************/
--===== Define I/O parameters
(
@pString VARCHAR(8000),
@pDelimiter CHAR(1)
)
RETURNS TABLE
AS
RETURN
WITH --"Inline" CTE Driven "Tally Table" produces values up to 10K... enough to cover VARCHAR(8000).
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),--10
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4)
--===== Do the split
SELECT ROW_NUMBER() OVER (ORDER BY N) AS ItemNumber,
SUBSTRING(@pString, N, CHARINDEX(@pDelimiter, @pString + @pDelimiter, N) - N) AS Item
FROM cteTally
WHERE N < LEN(@pString) + 2
AND SUBSTRING(@pDelimiter + @pString, N, 1) = @pDelimiter
;
Then use CROSS APPLY to return the rows that have a match, i.e.:
declare @test-2 TABLE (Col1 varchar(50))
insert into @test-2
select 'We are the masters of the universe' UNION ALL
select 'Database administrators are the best' UNION ALL
select 'Don''t drop the axe on your foot!' UNION ALL
select 'Steve runs a lot!' UNION ALL
select '...when he isn''t skiing.' UNION ALL
select 'Jeff doesn''t like RBAR code too much!'
select t.*
from @test-2 t
CROSS
APPLY DelimitedSplit8K('DROP DATABASE master', ' ') ds
where Col1 like '%' + ds.Item + '%'
Note that I deliberately used something that could be a result of SQL Injection, and it still processes that string into the individual words and gets the results for anything that has those individual words in the phrases in the @test-2 table.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply