Prev: question about replace function
Next: Index/Query Optimization Question
From: Roy Goldhammer on 11 Jul 2010 10:58
Hello there

the current sql:

declare @T varchar(100);

set @T = '[my name]'

select *
from dbo.TBL_clients
where client_name like '%' + @T + '%'
returns all the table

why?


From: Dan Guzman on 11 Jul 2010 11:36
According to the Books Online LIKE reference, '[]' matches any single
character within the specified list or set of characters. Your example will
return any client_name that contains the characters 'm','y',' ','n','a' and
'e' but not rows that contain only other characters. Below is an example of
this behavior.

CREATE TABLE dbo.clients(
client_name VARCHAR(100)
);

INSERT INTO dbo.clients(client_name)
VALUES ('John'),('Joe'),('Roy'),('Dan');

DECLARE @T varchar(100);

SET @T = '[Jim]';

SELECT client_name
FROM dbo.clients
WHERE client_name LIKE '%' + @T + '%';
GO

Returns only 'Joe' and 'John, but not Roy and Dan.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Roy Goldhammer" <royg(a)yahoo.com> wrote in message
news:eA7gSmQILHA.4824(a)TK2MSFTNGP05.phx.gbl...
> Hello there
>
> the current sql:
>
> declare @T varchar(100);
>
> set @T = '[my name]'
>
> select *
> from dbo.TBL_clients
> where client_name like '%' + @T + '%'
> returns all the table
>
> why?
>
From: Bob Barrows on 11 Jul 2010 11:53
Roy Goldhammer wrote:
> Hello there
>
> the current sql:
>
> declare @T varchar(100);
>
> set @T = '[my name]'
>
> select *
> from dbo.TBL_clients
> where client_name like '%' + @T + '%'
> returns all the table
>
> why?
This is a question you could have answered yourself by looking up "like
comparisons" in Books OnLine (BOL), where you would have seen this:

[ ]

Any single character within the specified range ([a-f]) or set ([abcdef]).

WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen
and starting with any single character between C and P, for example Carsen,
Larsen, Karsen, and so on.


So, your pattern is retrieving all rows where client_name contains the
characters m,y,space, n,a,e, which seems to include all the rows in your
table.
The solution:
eliminate the brackets, of course :-)


From: Dan on 12 Jul 2010 05:08

"Bob Barrows" <reb01501(a)yahoo.com> wrote in message
news:NNl_n.7371$Zp1.1644(a)newsfe15.iad...
> Roy Goldhammer wrote:
>> Hello there
>>
>> the current sql:
>>
>> declare @T varchar(100);
>>
>> set @T = '[my name]'
>>
>> select *
>> from dbo.TBL_clients
>> where client_name like '%' + @T + '%'
>> returns all the table
>>
>> why?
> This is a question you could have answered yourself by looking up "like
> comparisons" in Books OnLine (BOL), where you would have seen this:
>
> [ ]
>
> Any single character within the specified range ([a-f]) or set ([abcdef]).
>
> WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen
> and starting with any single character between C and P, for example
> Carsen, Larsen, Karsen, and so on.
>
>
> So, your pattern is retrieving all rows where client_name contains the
> characters m,y,space, n,a,e, which seems to include all the rows in your
> table.

> The solution:
> eliminate the brackets, of course :-)
>

Or use ESCAPE to define an escape character, and escape the brackets, if
they are required to be found in the rows ;)

--
Dan

 | 
Pages: 1
Prev: question about replace function
Next: Index/Query Optimization Question