Using single character wildcards in stored procedure and allow a %

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

danid12 - 22 Jul 2008 17:46 GMT

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)

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 22 Jul 2008 18:16 GMT

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

> We have a search form with multiple fields for searching. I've simplified
> the
[quoted text clipped - 26 lines]
> WHERE ee.LastName LIKE ISNULL(REPLACE(@LastName, '?', '_') + '%' ,
> ee.LastName)

Reply to this Message

danid12 - 22 Jul 2008 20:36 GMT

They're supposed to be able to use %, _, and ? in their searches.
However, they want to be able to have it do a Begins With without using
anything AND be able to use wildcards too. For example, entering 'SMIT'
would be the same as SMIT%. I don't see how that would be possible. They
should just enter SMIT% if that's what they mean. :)

Thanks for your assistance.

> Can't you teach your users two different wildcards?
>
[quoted text clipped - 71 lines]
> > WHERE ee.LastName LIKE ISNULL(REPLACE(@LastName, '?', '_') + '%' ,
> > ee.LastName)

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 22 Jul 2008 20:51 GMT

In one of the web interfaces I built ages ago, I had a dropdown that said:

EQUALS
CONTAINS
BEGINS WITH
ENDS WITH

When passing the parameter into the stored procedure, I simply added things
where necessary. If they chose "CONTAINS" and entered 'foo', I would pass
'%foo%'. If they chose "BEGINS WITH" I would pass 'foo%' etc.

Of course, they were free to use the wildcards within their search phrase
also, so they could choose "EQUALS" but enter 'foo_bar' or 'foo%bar'.

So, it could be done, but the user would have to pick whether they wanted
"begins with" to be functioning. It can't just happen automatically because
that can't always be what they want.

SQL injection always becomes an issue in cases like this, but this
specifically was an internal app where only a couple of people had
restricted access. I would probably program it much more carefully if I had
to re-do it today.

A

On 7/22/08 3:36 PM, in article
C57679C2-E672-4F82-9DD8-23CB4163A2F3@microsoft.com, "danid12"

> They're supposed to be able to use %, _, and ? in their searches.
> However, they want to be able to have it do a Begins With without using
[quoted text clipped - 79 lines]
>>> WHERE ee.LastName LIKE ISNULL(REPLACE(@LastName, '?', '_') + '%' ,
>>> ee.LastName)

Reply to this Message

danid12 - 22 Jul 2008 21:56 GMT

Thanks Aaron. We're going to end up just doing a Begins With even if they
use '_' or '?'. :)

> In one of the web interfaces I built ages ago, I had a dropdown that said:
>
[quoted text clipped - 107 lines]
> >>> WHERE ee.LastName LIKE ISNULL(REPLACE(@LastName, '?', '_') + '%' ,
> >>> ee.LastName)

Reply to this Message

Hugo Kornelis - 22 Jul 2008 22:02 GMT

(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

Signature

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Reply to this Message

danid12 - 22 Jul 2008 22:19 GMT

I was trying to do a CASE statement, but couldn't get it to work. I pasted
yours into my stored procedure and it works. Thank you!

> (snip)
> >The way that I have it below, if they search for SMIT_, they will get SMITH,
[quoted text clipped - 11 lines]
> WHERE ee.LastName LIKE REPLACE (@LastName, '?', '_')
> + CASE WHEN @LastName NOT LIKE '%[%_?]%' THEN '%' ELSE '' END

Reply to this Message