Prev: Database Engine Tuning Advisor
Next: Procedure to insert into multiple tables
From: danid12 on 22 Jul 2008 12:46
We have a search form with multiple fields for searching. I've simplified the
stored procedure below to only show a couple of fields and parameters.

The users can search using the ? or _ for single character searches by
entering them in the form. However, they also want to be able to use the
wildcard % to use Begins With. How can I allow all of this in my stored
procedure?

The way that I have it below, if they search for SMIT_, they will get SMITH,
but they will also get SMITTY, SMITHSON, etc...due to the %. I have not
figured out a way to allow them to do both a single character search with ?
or _ and a Begins With as well.

CREATE PROCEDURE [dbo].[EmployeeSearch]
@LastName varchar(20) = Null,
@FirstName varchar(15) = Null,
AS

SET ARITHABORT ON

SELECT DISTINCT
RTRIM(ee.LastName + ', ' + ee.FirstName + ' ' + ee.MI) AS [Name],
ee.LastName,
ee.FirstName
FROM Employee ee
WHERE ee.LastName LIKE ISNULL(REPLACE(@LastName, '?', '_') + '%' ,
ee.LastName)

From: Aaron Bertrand [SQL Server MVP] on 22 Jul 2008 13:16
Can't you teach your users two different wildcards?


USE tempdb;
GO

CREATE TABLE dbo.Employees
(
LastName VARCHAR(32)
);
GO

SET NOCOUNT ON;

INSERT dbo.Employees(LastName)
SELECT 'SMITH'
UNION ALL SELECT 'SMITTY'
UNION ALL SELECT 'SMITHSON';
GO

CREATE PROCEDURE dbo.EmployeeSearch
@LastName VARCHAR(32) = NULL
AS
BEGIN
SET NOCOUNT ON;

SET @LastName = REPLACE(@LastName, '?', '_');

SELECT LastName
FROM dbo.Employees
WHERE LastName LIKE COALESCE(@LastName, LastName);
END
GO

EXEC dbo.EmployeeSearch 'SMIT?';
EXEC dbo.EmployeeSearch 'SMIT%';
EXEC dbo.EmployeeSearch 'SM%';
EXEC dbo.EmployeeSearch '%T_';
EXEC dbo.EmployeeSearch '%X%';
GO

DROP TABLE dbo.Employees;
GO
DROP PROCEDURE dbo.EmployeeSearch;
GO





"danid12" <danid12(a)discussions.microsoft.com> wrote in message
news:DB8E40CF-E277-49DF-B2E2-D795FC9A5E33(a)microsoft.com...
> We have a search form with multiple fields for searching. I've simplified
> the
> stored procedure below to only show a couple of fields and parameters.
>
> The users can search using the ? or _ for single character searches by
> entering them in the form. However, they also want to be able to use the
> wildcard % to use Begins With. How can I allow all of this in my stored
> procedure?
>
> The way that I have it below, if they search for SMIT_, they will get
> SMITH,
> but they will also get SMITTY, SMITHSON, etc...due to the %. I have not
> figured out a way to allow them to do both a single character search with
> ?
> or _ and a Begins With as well.
>
> CREATE PROCEDURE [dbo].[EmployeeSearch]
> @LastName varchar(20) = Null,
> @FirstName varchar(15) = Null,
> AS
>
> SET ARITHABORT ON
>
> SELECT DISTINCT
> RTRIM(ee.LastName + ', ' + ee.FirstName + ' ' + ee.MI) AS [Name],
> ee.LastName,
> ee.FirstName
> FROM Employee ee
> WHERE ee.LastName LIKE ISNULL(REPLACE(@LastName, '?', '_') + '%' ,
> ee.LastName)
>

From: Hugo Kornelis on 22 Jul 2008 17:02
On Tue, 22 Jul 2008 09:46:00 -0700, danid12 wrote:

(snip)
>The way that I have it below, if they search for SMIT_, they will get SMITH,
>but they will also get SMITTY, SMITHSON, etc...due to the %. I have not
>figured out a way to allow them to do both a single character search with ?
>or _ and a Begins With as well.

Hi danid12,

I agree with Aaron that it would be much cleaner if you either teach
your users to enter % or specify the kind of search. But it *is*
possible to have a search for "begins with" if no wildcard characters
are specified, or for the specified pattern if they are:

WHERE ee.LastName LIKE REPLACE (@LastName, '?', '_')
+ CASE WHEN @LastName NOT LIKE '%[%_?]%' THEN '%' ELSE '' END

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
 | 
Pages: 1
Prev: Database Engine Tuning Advisor
Next: Procedure to insert into multiple tables