Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Searching string fields. OPENXML

Status
Not open for further replies.

Casbah

Programmer
May 18, 2004
31
IE
On a site I have a search box.
Currently the way this works is the user enters a value and I append % to the start and end of the value and search against the appropriate columns.
However this sort of basic search will miss a lot of values. For example a search of "term1 and term2" will miss a value of "term1, term2". What I would like to do is pass each word in to my stored procedure.
I believe the best way to do this is using OPENXML. My Stored Procedure currently looks like this

SELECT blah
FROM Company
WHERE name LIKE
(
SELECT SearchTerm
FROM OPENXML (@hdoc, '/File/Item', 1)
WITH (SearchTerm nvarchar(20))
)
OR desc LIKE
(
SELECT SearchTerm
FROM OPENXML (@hdoc, '/File/Item', 1)
WITH (SearchTerm nvarchar(20))
)

However if I have more than one value in my XML file I get an error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So I tried changing the LIKE clause in the query to IN, which removed the error, but now only exact matches are returned.
Has anybody got a suggestion of what I should be doing?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top