[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 737
  • Last Modified:

Multiple word Search statement

Hello, I'm using the following in an attmpt to seach one or all of four columns for a word or words. Similar to a google search. It works for a single word but does not for multiple words. SQL 2005

How can my statement be modified to work with multile words???

My sql server is on a hosted site where I cannot use contains...
SELECT * FROM mm_articles
where (rtrim(@SearchString)  is NUll or rtrim(Title)  like rtrim('%' + @SearchString) + '%')
or (rtrim(@SearchString)  is NUll or rtrim(SubTitle)  like rtrim(@SearchString) + '%')
or (rtrim(@SearchString)  is NUll or rtrim(Intro)  like rtrim('%' + (@SearchString) + '%'))
or (rtrim(@SearchString)  is NUll or rtrim(body)  like rtrim(@SearchString) + '%')
and Active = 'Yes'

Open in new window

0
gogetsome
Asked:
gogetsome
1 Solution
 
Anthony PerkinsCommented:
You will have to split the words into a table and then search against them.
0
 
Anthony PerkinsCommented:
Something like this:

Declare @SearchString varchar(500)                              -- Modify data type as necessary.
Set @SearchString = LTRIM(RTRIM(@SearchString))

Declare @Search Table (SearchString varchar(50))

Insert      @Search(SearchString)
Select      '%' + SearchString + '%'
From      udf_UseYourFavoriteSplitFunction(@SearchString)

SELECT      *
FROM      mm_articles a
            Left Join @Search s On a.Title Like s.SearchString
                                          Or a.SubTitle Like s.SearchString
                                          Or Intro Like s.SearchString
                                          Or Body Like s.SearchString
and Active = 'Yes'
0
 
gogetsomeAuthor Commented:
Hello acperkins, I do not have a udf_useyour favoritesplitfunction. Where can I find one?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gogetsomeAuthor Commented:
Would something like this work?

CREATE FUNCTION dbo.Split
(
      @RowData nvarchar(2000),
      @SplitOn nvarchar(5)
)  
RETURNS @RtnValue table
(
      Id int identity(1,1),
      Data nvarchar(100)
)
AS  
BEGIN
      Declare @Cnt int
      Set @Cnt = 1

      While (Charindex(@SplitOn,@RowData)>0)
      Begin
            Insert Into @RtnValue (data)
            Select
                  Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))

            Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
            Set @Cnt = @Cnt + 1
      End
      
      Insert Into @RtnValue (data)
      Select Data = ltrim(rtrim(@RowData))

      Return
END
0
 
Hemantgiri S. GoswamiCommented:
Hi,

--@SrcWord = Search a Word
--@String = Record / value /sentence from we have to find the word
declare @srcword varchar(20),@srcword2 varchar(20),@string varchar(2000),@string2 varchar(2000),@item varchar(8000),@item2 varchar(8000),@length int,@length2 int
set @srcword ='all'
set @srcword2 ='Sybase'
set @length = len(@srcword)
set @length2 = len(@srcword2)
set @string ='Good Morning all Myself Hemantgiri S Goswami'
set @string2 ='Sybase Professional Services'
CREATE TABLE #findword(Item varchar(8000)) -- Create a temporary table
CREATE TABLE #findword2(Item varchar(8000))

SELECT
@Item=@String,
@String=(SUBSTRING(@String,CHARINDEX(@SrcWord,@String,0)+0,@length))

select
@item2=@string2, --holding a value of string/sentence
@String2=(SUBSTRING(@String2,CHARINDEX(@SrcWord2,@String2,0)+0,@length2)) --this will give search value

INSERT INTO #findword SELECT @Item
INSERT INTO #findword SELECT @String -- Put the search value

INSERT INTO #findword2 SELECT @Item2
INSERT INTO #findword2 SELECT @String2 -- Put the search value

SELECT * FROM #findword
SELECT * FROM #findword2

DROP TABLE #findword
DROP TABLE #findword2
0
 
gogetsomeAuthor Commented:
Interesting ghemant, but how do I apply your code to my situation. Sorry I don't understand...
0
 
Anthony PerkinsCommented:
>>Would something like this work?<<
I don't know.  Have you tried it?
0
 
gogetsomeAuthor Commented:
Thanks for your help acperkins.

When I use the split function as shown above with the statment you created it thows the error below. From the error I think I should modify the split funtion to take the correct parameters??

This is the error I get when I run the statement below:
Msg 313, Level 16, State 3, Line 6
An insufficient number of arguments were supplied for the procedure or function split.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'SearchString'.


Declare @SearchString varchar(500)                              -- Modify data type as necessary.
Set @SearchString = LTRIM(RTRIM(@SearchString))

Declare @Search Table (SearchString varchar(50))

Insert      @Search(SearchString)
Select      '%' + SearchString + '%'
From      split(@SearchString)

SELECT      *
FROM      mm_articles a
            Left Join @Search s On a.Title Like s.SearchString
                                          Or a.SubTitle Like s.SearchString
                                          Or Intro Like s.SearchString
                                          Or Body Like s.SearchString
and Active = 'Yes'
0
 
Anthony PerkinsCommented:
Assuming that Split function is correct for your data (without seeing it, I can only guess) than do it as follows:

Declare @SearchString varchar(500)                              -- Modify data type as necessary.
Set @SearchString = LTRIM(RTRIM(@SearchString))

Declare @Search Table (SearchString varchar(50))

Insert      @Search(SearchString)
Select      '%' + SearchString + '%'
From      dbo.Split(@SearchString, ' ')

SELECT      *
FROM      mm_articles a
            Left Join @Search s On a.Title Like s.SearchString
                                          Or a.SubTitle Like s.SearchString
                                          Or Intro Like s.SearchString
                                          Or Body Like s.SearchString
and Active = 'Yes'
0
 
hemaldesaiCommented:
when i run this, it results in all the records being displayed.
ALTER PROCEDURE [dbo].[sp_Product_Search]
	@Title varchar(max) output,
	@IsActive bit output
AS
 
Declare @SearchString varchar(max)                              -- Modify data type as necessary.
Set @SearchString = LTRIM(RTRIM(@Title))
 
Declare @Search Table (SearchString varchar(50))
 
Insert      @Search(SearchString) 
Select      '%' + strName + '%'
From      dbo.Split(@SearchString, ' ')
 
 
	SELECT        ProductID, CategoryID, SecondCategoryID, ThirdCategoryID, FourthCategoryID, FifthCategoryID, SixthCategoryID, AddedDate, AddedBy, Title, Description, 
                         SKU, Measurements, UnitPrice, DiscountPercentage, UnitsInStock, SmallImageUrl, FullImageURL, IsActive, ShowInWhatsNew
FROM            tbl_Products a
            Left Join @Search s On a.Title Like s.SearchString 
            Or a.SKU Like s.SearchString
                                          Or Description Like s.SearchString
and (IsActive = IsNull(@IsActive,IsActive))

Open in new window

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now