Prev: Using RETURN
Next: Database is in Transistion. There is already an open datareader as
From: danid12 on 22 Jul 2008 15:36
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.


"Aaron Bertrand [SQL Server MVP]" wrote:

> 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: Aaron Bertrand [SQL Server MVP] on 22 Jul 2008 15:51
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(a)microsoft.com, "danid12"
<danid12(a)discussions.microsoft.com> wrote:

> 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.
>
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>> 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: danid12 on 22 Jul 2008 16:56
Thanks Aaron. We're going to end up just doing a Begins With even if they
use '_' or '?'. :)


"Aaron Bertrand [SQL Server MVP]" wrote:

> 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(a)microsoft.com, "danid12"
> <danid12(a)discussions.microsoft.com> wrote:
>
> > 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.
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> 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: danid12 on 22 Jul 2008 17:19
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!

"Hugo Kornelis" wrote:

> 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: Using RETURN
Next: Database is in Transistion. There is already an open datareader as