July 10, 2009 at 3:07 am
Users are unable to serach using wild cards on a web form. The stored procedure is as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[bsp_intranet_product_search]
@ProductId NVARCHAR(100),
@ProductDescription NVARCHAR(300)
AS
SELECT DISTINCT TOP 100 ProductId, ProductDescription, CrossReference,
ISNULL(CONVERT(VARCHAR,SUM(QuantityOutstanding)),'0') AS Quantity,
'£' + CONVERT (VARCHAR(12),StandardPrice,1) AS StandardPrice
FROM Products
LEFT OUTER JOIN dbo.Inventory ON dbo.Inventory.Product= dbo.Products.Product
WHERE (IsNull(RTrim(@ProductId), '') = '' OR [ProductId] LIKE '%' + RTrim(@ProductId) + '%')
AND (IsNull(RTrim(@ProductDescription), '') = '' OR [ProductDescription] LIKE '%' + RTrim(@ProductDescription) + '%')
GROUP BY Products.ProductId, Products.ProductDescription, Products.CrossReference, Products.StandardPrice
I have tried variations on the above code. If I change the AND to OR in the WHERE clause then nothing is returned. I was given help on this previously by this forum.
Basically the user wants to be able to enter part of a product code or description i.e.
680* or *680 or *Screw or Screw* etc...
Any Ideas?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
July 10, 2009 at 6:25 am
2Tall (7/10/2009)
Users are unable to serach using wild cards on a web form. The stored procedure is as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[bsp_intranet_product_search]
@ProductId NVARCHAR(100),
@ProductDescription NVARCHAR(300)
AS
SELECT DISTINCT TOP 100 ProductId, ProductDescription, CrossReference,
ISNULL(CONVERT(VARCHAR,SUM(QuantityOutstanding)),'0') AS Quantity,
'£' + CONVERT (VARCHAR(12),StandardPrice,1) AS StandardPrice
FROM Products
LEFT OUTER JOIN dbo.Inventory ON dbo.Inventory.Product= dbo.Products.Product
WHERE (IsNull(RTrim(@ProductId), '') = '' OR [ProductId] LIKE '%' + RTrim(@ProductId) + '%')
AND (IsNull(RTrim(@ProductDescription), '') = '' OR [ProductDescription] LIKE '%' + RTrim(@ProductDescription) + '%')
GROUP BY Products.ProductId, Products.ProductDescription, Products.CrossReference, Products.StandardPrice
I have tried variations on the above code. If I change the AND to OR in the WHERE clause then nothing is returned. I was given help on this previously by this forum.
Basically the user wants to be able to enter part of a product code or description i.e.
680* or *680 or *Screw or Screw* etc...
Any Ideas?
Thanks,
Phil.
Greetings Phil,
If you are using the asterisk (*) as your wildcard on the web page, then you would need to search for the asterisk in your search criteria and change it to the percent sign (%). Otherwise, your search will fail every time since you have the wrong wildcard inserted.
You could do something like this:
DECLARE @StartPos int
DECLARE @CurrPos int
DECLARE @SearchString varchar(max)
DECLARE @SearchID varchar(100)
DECLARE @SearchDesc varchar(300)
SELECT
@StartPos = 1,
@CurrPos = CHARINDEX('*', @ProductID, 1),
@SearchString = '',
@ProductID = ISNULL(@ProductID, ''),
@ProductDescription = ISNULL(@ProductDescription, '')
WHILE @CurrPos > 0
BEGIN
IF (@CurrPos = @StartPos)
BEGIN
SELECT
@SearchString = @SearchString + '%'
END
ELSE
BEGIN
SELECT
@SearchString = @SearchString + SUBSTRING(@ProductID, @StartPos, (@CurrPos - @StartPos)) + '%'
END
SELECT
@StartPos = @CurrPos + 1
SELECT
@CurrPos = CHARINDEX('*', @ProductID, @StartPos)
END
SELECT
@SearchString = @SearchString + SUBSTRING(@ProductID, @StartPos, (LEN(@ProductID) - @StartPos + 1))
SELECT
@SearchID = @SearchString + '%'
SELECT
@StartPos = 1,
@CurrPos = CHARINDEX('*', @ProductDescription, 1),
@SearchString = ''
WHILE @CurrPos > 0
BEGIN
IF (@CurrPos = @StartPos)
BEGIN
SELECT
@SearchString = @SearchString + '%'
END
ELSE
BEGIN
SELECT
@SearchString = @SearchString + SUBSTRING(@ProductDescription, @StartPos, (@CurrPos - @StartPos)) + '%'
END
SELECT
@StartPos = @CurrPos + 1
SELECT
@CurrPos = CHARINDEX('*', @ProductDescription, @StartPos)
END
SELECT
@SearchString = @SearchString + SUBSTRING(@ProductDescription, @StartPos, (LEN(@ProductID) - @StartPos + 1))
SELECT
@SearchDesc = @SearchString + '%'
SELECT DISTINCT TOP 100
ProductId,
ProductDescription,
CrossReference,
ISNULL(CONVERT(VARCHAR,SUM(QuantityOutstanding)),'0') AS Quantity,
'£' + CONVERT (VARCHAR(12),StandardPrice,1) AS StandardPrice
FROM Products
LEFT OUTER JOIN dbo.Inventory ON dbo.Inventory.Product= dbo.Products.Product
WHERE
ProductID LIKE @SearchID
OR ProductDescription LIKE @SearchDesc
GROUP BY
Products.ProductId,
Products.ProductDescription,
Products.CrossReference,
Products.StandardPrice
Have a good day.
Terry Steadman
July 10, 2009 at 8:32 am
Terry many thanks for taking time to respond to my post.
I will try the code posted.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
July 10, 2009 at 10:18 am
If you just need to handle the '*' wild-cards you can use something like this:
DECLARE @prods TABLE (Id INT IDENTITY, Name VARCHAR(100))
INSERT INTO @prods
SELECT 'Prod1'
UNION ALL SELECT 'This is another prod'
UNION ALL SELECT 'Yet another one'
DECLARE @search VARCHAR(100)
SELECT @search = 'ano*p'
SELECT
*
FROM @prods
WHERE ISNULL(@search, '') = ''
OR Name LIKE '%' + REPLACE(@search, '*', '%') + '%'
BUT
This type of queries is known as catch-all queries which produce a really bad execution plan. Please have a look to Gail's blog:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
July 11, 2009 at 2:58 am
Thanks. I must say I thought adding 'Search' to the web form was going to be simple! 🙂
They had an old site running on MySQL that worked fine.
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
July 11, 2009 at 3:31 am
Maybe, sometimes I think too big. 😉
Adding a search to a smaller web page isn't that hard. Replace all '*' with '%', add a trailing '%' and (if needed) a trailing '%'. Done.
If you need a leading '%' you already work without index usage. The catch-all syntax shouldn't affect the performance any more. For some thousand rows this should be no huge problem. (Just tried with 10.000 rows and a duration of 9ms.)
Just keep in mind and a bookmark in your browser to know if you ever get performance problems.
If I look at your column names I would suggest to:
* use a equality search (no wild-cards) for ProductId
* use a FULLTEXT INDEX for ProductDescription
July 11, 2009 at 8:35 am
Just want to add...for very large tables, wild-card searches (particularly leading wild card) will perform far better with full text indexing.
edit: whoops, didn't see u already said that :hehe:
July 11, 2009 at 2:43 pm
Thanks to all. The table in question has 34000 rows.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
July 11, 2009 at 2:47 pm
Thanks for the feedback. Glad we could help!
July 14, 2009 at 4:32 am
Hi Florian.
DECLARE @prods TABLE (Id INT IDENTITY, Name VARCHAR(100))
INSERT INTO @prods
SELECT 'Prod1'
UNION ALL SELECT 'This is another prod'
UNION ALL SELECT 'Yet another one'
DECLARE @search VARCHAR(100)
SELECT @search = 'ano*p'
SELECT
*
FROM @prods
WHERE ISNULL(@search, '') = ''
OR Name LIKE '%' + REPLACE(@search, '*', '%') + '%'
Can you advise what the code is doing? Is it creating a table prods in the database?
At the moment I have a gridview on the form. The datasource uses a stored procedure (my original post). The parameters within the stored procedure map to the buttons on the web form (parameter source).
I have attached screen dump.
Bascially how do I replace my stored proc with your code to enable wild card search?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
October 6, 2009 at 9:25 am
Hello again. I have been asked to include an additional field that users can search on.
I have attached a script that will create the required tables + add some sample data.
I have included a version of the stored procedure as a SELECT query.
What do I want to do? Currently users can search by ProductId or ProductDescription from Products table. I hoped to concatenate the Product Description with a field from another table to create a modified ProductDescription that users could search on. So the end result would be is a user searched on 'CC364%' the query would return ProductId + the concatenated description.
At present if I search on 'CC364%' the query returns 0 rows. If I search on '%4014%' the query returns 1 row (4014 is part of the standard product description, CC364 is part of the 'TheirProduct').
[Code]
SELECT DISTINCT TOP 100 p.ProductId, p.ProductDescription + ' ' + ISNULL(pcr.TheirProduct,'') AS ProductDescription,pcr.TheirProduct
FROM Products AS p
LEFT OUTER JOIN dbo.ProductCrossReference AS pcr ON p.Product = pcr.Product
WHERE (IsNull(RTrim(@ProductId), '') = '' OR [ProductId] LIKE RTrim(@ProductId) + '%')
AND (IsNull(RTrim(@ProductDescription), '') = '' OR [ProductDescription] LIKE RTrim(@ProductDescription) + '%')
GROUP BY p.ProductId, p.ProductDescription,pcr.TheirProduct
ORDER BY p.ProductId ASC
[/Code]
I hope that makes sense.
Any help appreciated.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply