where clause from split string search 1

[davejam]

Hi all,

I am writing a webpage that has a search that simply builds the search with entered words. I have compiled this so pipe symbols are between the search words.

I then want to submit this to a sproc to then bring back the data.

Is there a way to write an inline query for each of these keywords without having to 'build' my query and execute.

I am very against building the query as a string and executing this!

is there a way to split the string into an array and search for these as a 'like'

for example @String = 'sony|stereo'

I want to search for any product that has 'sony' + 'stereo' in the description

the site is public facing so am very aware of sql injection, also I would prefer to have inline sql as it makes the database work faster and more efficiant in the long term!

Any ideas

Cheers

daveJam

easy come, easy go!!!

 

[bborissov]

if you can change the pipe with percent sign, If you can't try this:

SELECT * from YourTable
WHERE SomeField like '%'+REPLACE(@String,'|','%')+'%'

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

1. You need to split the words into a table (you may want to use any of the splitting functions available, in particular, I like recent blog by Jeff Moden

http://www.sqlservercentral.com/articles/Tally+Table/72993/

2. Once you split your data into table, it's simple:

select Value as Word into #Words
from dbo.fnSplit(@String, '|')

;with cte as (select *, count(Word) over (partition by T.Pk) as cntWords  from myTable T INNER JOIN #Words W on T.Description LIKE
'%' + W.Word + '%'
)

select * from cte where cntWords = (select count(*) from #Words)

HAVING

PluralSight Learning Library

 

[davejam]

that looks interesting makros... i will look into this, i think i may have had an issue with table variables in select statements but i think that may have been from an exec build which is obviously what i am trying to avoid...

i will let you know how i get on!!

daveJam

easy come, easy go!!!

 

[davejam]

Cheers, cracked it...

I was having to find items that have the search terms in any part of the description which made it all a little harder so did it in a few steps

-- 1. get the search items into a table variable
DECLARE @GetSearch TABLE 
(
 srcWord nvarchar(50)
)
INSERT INTO @GETSEARCH (srcWord)
select Value as srcWord
from dbo.fnSplit(@srcVal, '|')

-- 2. get the count of words, this way i can only get records where all the words appear
DECLARE @Count int
SELECT @Count = count(srcWord) from @GetSearch

-- 3. build a table variable of all items that have the match of all searched words.. this is so i can easilly link on the id within this
DECLARE @CompSearch TABLE 
(
 myID decimal(16,0)
)
INSERT INTO @CompSearch (myID)
  SELECT A.ID
  FROM tableItem AS A 
   INNER JOIN @GETSEARCH as B on a.description COLLATE SQL_Latin1_General_CP1_CI_AS like '%' + b.srcWord + '%' 
    OR a.moreDescription COLLATE SQL_Latin1_General_CP1_CI_AS like '%' + b.srcWord + '%' 
group by A.ID
having count(b.srcWord) = @Count

-- 4.now get my items with the link
SELECT a.* FROM tableItem A INNER JOIN @CompSearch B on a.ID = b.myID

I had to add some collation for the match as it kicked up a fuss....

I also had to create the fnSplit, but found some code as a base simply searching on t'internet!! This has created a very handy multi search with actually quite small effort...

This can also be adopted to search when people enter multiple words but may not necessarilly want the exact word formation match!!

I'm sure this can be done in less statements but i am quite happy with the result, and using SET NOCOUNT ON; SET NOCOUNT OFF;
it has actually speeded up the use of the sites data retrieval!

Hope this helps if someone is looking for the same action

Cheers

daveJam

easy come, easy go!!!