how to extend search

[kognitio]

Hello, (sorry about my english, I am german)
I am modyfied an Starter-Kit from asp.net, called Commerce Starter Kit and everything is fine except the search-function. The problem is, that the search works only with as an "exact" match. Not exact at all. When I am typing in "battery" then I get all words which contains "battery" or they are "battery". If I am typing in "battery charger" I get nothing, because there is not sentence where "charger" follows DIRECTLY "battery". It is more like "The battery from the .... and the charger ..." In that case I am getting no results. How can I solve that problem?
The code is like that:

ALTER Procedure CMRC_ProductSearch
(
    @Search nvarchar(255)
)
AS


SELECT     ProductID, ModelName, ModelNumber, UnitCost, Beschreibung, ProductImage, Volt
FROM         CMRC_Products
WHERE     
Beschreibung LIKE '%' + @Search + '%' 
OR
ProductImage LIKE '%' + @Search + '%'

Does anyone know what to do ...?
Thanks for helping,
Stephan

P.S.: There is a Online-Documentation. But I didn't find that much about search-functions:

 

[SQLSister]

Will you be searching a large recordset? Becasue this search methodolgoy is not efficient becasue using a wildcard as the first character means that indexes cannot be used.

Using this method, what you need is to split the search phrase into separate words and then put the individual words into the where clause. The following FAQs willhelp with this.
Passing a list of values to a Stored Procedure faq183-3979 Passing a list of values to a Stored Procedure (Part II) faq183-5207

Your other option is to use full-text search and the freetext keyword to get the answers. Full-text search is a complex subject and you should read about it in BOL.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[kognitio]

wow quick response ...
thank you!
I will read that and try to do my best.
Stephan

 

[kognitio]

hello, it is me again.
I didn't have the time to solve that problem but today I wanted to do that. I took the split funktion from here and modyfied the code like that:

ALTER Procedure CMRC_ProductSearch
(
    @Search nvarchar(255)
)
AS

CREATE Function Split(
   @InputText Varchar(4000), -- The text to be split into rows
   @Delimiter Varchar(10)) -- The delimiter that separates tokens.
                           -- Can be multiple characters, or empty

RETURNS @Array TABLE (
   TokenID Int PRIMARY KEY IDENTITY(1,1), --Comment out this line if
                                          -- you don't want the
                                          -- identity column
   Value Varchar(4000))

AS

-----------------------------------------------------------
-- Function Split                                        --
--    • Returns a Varchar rowset from a delimited string --
-----------------------------------------------------------

BEGIN

   DECLARE
      @Pos Int,        -- Start of token or character
      @End Int,        -- End of token
      @TextLength Int, -- Length of input text
      @DelimLength Int -- Length of delimiter

-- Len ignores trailing spaces, thus the use of DataLength.
-- Note: if you switch to NVarchar output, you'll need to divide by 2.
   SET @TextLength = DataLength(@InputText)

-- Exit function if no text is passed in
   IF @TextLength = 0 RETURN

   SET @Pos = 1
   SET @DelimLength = DataLength(@Delimiter)

   IF @DelimLength = 0 BEGIN -- Each character in its own row
      WHILE @Pos <= @TextLength BEGIN
         INSERT @Array (Value) VALUES (SubString(@InputText,@Pos,1))
         SET @Pos = @Pos + 1
      END
   END
   ELSE BEGIN
      -- Tack on delimiter to 'see' the last token
      SET @InputText = @InputText + @Delimiter
      -- Find the end character of the first token
      SET @End = CharIndex(@Delimiter, @InputText)
      WHILE @End > 0 BEGIN
         -- End > 0, a delimiter was found: there is a(nother) token
         INSERT @Array (Value) VALUES (SubString(@InputText, @Pos, @End - @Pos))
         -- Set next search to start after the previous token
         SET @Pos = @End + @DelimLength
         -- Find the end character of the next token
         SET @End = CharIndex(@Delimiter, @InputText, @Pos)
      END
   END
   
   RETURN

END

GO

SELECT		ProductID, 
			ModelName,
			ModelNumber, 
			UnitCost, 
			Beschreibung, 
			ProductImage, 
			Volt
FROM         CMRC_Products
WHERE     
Beschreibung LIKE '% ' + @Search + ' %' 
OR
ProductImage LIKE '%' + @Search + '%'

but I got an error-message that I have to declare the variables @InputText @Delimiter @Array and there must be an syntax error in the near from "GO".
I am a SQL-Newbi and have no idea what is wrong, because I thought that this is a declaration:

@InputText Varchar(4000)

Is it that I cannot insert the function here? How do I have to declare the avriables?
Thanks for helping,
Stephan