Wildcard doesn't match using LIKE '%' on a varchar field, wierd!

[Niall Porter]

Right this has to be a Micro$oft mess-up surely...?

I'm running SQL 2k standard with SP3. I have a table which I'm trying
to query using a LIKE operator on a varchar field as follows

....
WHERE dbo.tbl_pm_proj ects.SeniorMana gerID LIKE '%'
....

In actual fact the % is passed in by the application when the user
selects "All managers" from the drop down list used to select the
Manager to filter by. If they select a manager's name from the list
it becomes LIKE 'ajames' or whatever.

BUT - the table currently contains 2972 records. If I take out the
WHERE clause the SELECT returns all records - fine - but if I put the
where clause in it returns only 1682!! I thought the % was meant to
match, and I quote the SQL server Books Online files here; "Any string
of zero or more characters." Anyone explain to me whats going on
here?

TIA
Niall

[aaj]

you have probably already looked at this... bit I'm just wondering if
there's a pattern to the things its not returning?

if you subrtract your results from the 'like' records from the 'full' set of
records, is there anything in the SeniorManagerID field that suffests a
pattern?


"Niall Porter" <ni*********@ya hoo.co.uk> wrote in message
news:2d******** *************** ***@posting.goo gle.com...

Right this has to be a Micro$oft mess-up surely...?

I'm running SQL 2k standard with SP3. I have a table which I'm trying
to query using a LIKE operator on a varchar field as follows

...
WHERE dbo.tbl_pm_proj ects.SeniorMana gerID LIKE '%'
...

In actual fact the % is passed in by the application when the user
selects "All managers" from the drop down list used to select the
Manager to filter by. If they select a manager's name from the list
it becomes LIKE 'ajames' or whatever.

BUT - the table currently contains 2972 records. If I take out the
WHERE clause the SELECT returns all records - fine - but if I put the
where clause in it returns only 1682!! I thought the % was meant to
match, and I quote the SQL server Books Online files here; "Any string
of zero or more characters." Anyone explain to me whats going on
here?

TIA
Niall

[Ronnie Chee]

"Niall Porter" <ni*********@ya hoo.co.uk> wrote in message
news:2d******** *************** ***@posting.goo gle.com...

Right this has to be a Micro$oft mess-up surely...?

I'm running SQL 2k standard with SP3. I have a table which I'm trying
to query using a LIKE operator on a varchar field as follows

...
WHERE dbo.tbl_pm_proj ects.SeniorMana gerID LIKE '%'
...

In actual fact the % is passed in by the application when the user
selects "All managers" from the drop down list used to select the
Manager to filter by. If they select a manager's name from the list
it becomes LIKE 'ajames' or whatever.

BUT - the table currently contains 2972 records. If I take out the
WHERE clause the SELECT returns all records - fine - but if I put the
where clause in it returns only 1682!! I thought the % was meant to
match, and I quote the SQL server Books Online files here; "Any string
of zero or more characters." Anyone explain to me whats going on
here?

TIA
Niall

Is the column null-able?
Try
....
WHERE dbo.tbl_pm_proj ects.SeniorMana gerID is NULL
....
and see if it returns anything.

[Erland Sommarskog]

Niall Porter (ni*********@ya hoo.co.uk) writes:

Right this has to be a Micro$oft mess-up surely...?
I would not place a bet on that.
I'm running SQL 2k standard with SP3. I have a table which I'm trying
to query using a LIKE operator on a varchar field as follows

...
WHERE dbo.tbl_pm_proj ects.SeniorMana gerID LIKE '%'
...

In actual fact the % is passed in by the application when the user
selects "All managers" from the drop down list used to select the
Manager to filter by. If they select a manager's name from the list
it becomes LIKE 'ajames' or whatever.

BUT - the table currently contains 2972 records. If I take out the
WHERE clause the SELECT returns all records - fine - but if I put the
where clause in it returns only 1682!! I thought the % was meant to
match, and I quote the SQL server Books Online files here; "Any string
of zero or more characters." Anyone explain to me whats going on
here?

Any string, yes, but if there are 2972 - 1682 rows in that table where
SeniorManagerID is NULL, it all makes sense.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ross Presser]

ni*********@yah oo.co.uk (Niall Porter) wrote in
news:2d******** *************** ***@posting.goo gle.com:

Right this has to be a Micro$oft mess-up surely...?

I'm running SQL 2k standard with SP3. I have a table which I'm trying
to query using a LIKE operator on a varchar field as follows

...
WHERE dbo.tbl_pm_proj ects.SeniorMana gerID LIKE '%'
...

In actual fact the % is passed in by the application when the user
selects "All managers" from the drop down list used to select the
Manager to filter by. If they select a manager's name from the list
it becomes LIKE 'ajames' or whatever.

BUT - the table currently contains 2972 records. If I take out the
WHERE clause the SELECT returns all records - fine - but if I put the
where clause in it returns only 1682!! I thought the % was meant to
match, and I quote the SQL server Books Online files here; "Any string
of zero or more characters." Anyone explain to me whats going on
here?

TIA
Niall

Beating a dead horse here -- it's already been answered by others. But, a
NULL value is *not* the same as a string of zero length. NULL represents
the absence of any value, and the SQL standard says that a NULL value is
never equal to any other value including another NULL.

[Niall Porter]

Thanks all for your replies. I checked the number of records returned
by the full query and the one with the LIKE '%' condition and indeed,
if I do it with WHERE dbo.tbl_pm_proj ects.SeniorMana gerID IS NULL then
I get 1290 records. 1290 + 1682 = 2972, the total number of records
in the table.

I must admit I didn't check this to start with because I'm sure this
query was returning all entries in the table previously. Is there
another wildcard or operator I can use or some config setting in the
database or the SQL server in general I can use to have it include
NULL results in queries like this?

Niall

ni*********@yah oo.co.uk (Niall Porter) wrote in message news:<2d******* *************** ****@posting.go ogle.com>...

Right this has to be a Micro$oft mess-up surely...?

I'm running SQL 2k standard with SP3. I have a table which I'm trying
to query using a LIKE operator on a varchar field as follows

...
WHERE dbo.tbl_pm_proj ects.SeniorMana gerID LIKE '%'
...

In actual fact the % is passed in by the application when the user
selects "All managers" from the drop down list used to select the
Manager to filter by. If they select a manager's name from the list
it becomes LIKE 'ajames' or whatever.

BUT - the table currently contains 2972 records. If I take out the
WHERE clause the SELECT returns all records - fine - but if I put the
where clause in it returns only 1682!! I thought the % was meant to
match, and I quote the SQL server Books Online files here; "Any string
of zero or more characters." Anyone explain to me whats going on
here?

TIA
Niall

[Hugo Kornelis]

On 6 Sep 2004 03:07:49 -0700, Niall Porter wrote:

Thanks all for your replies. I checked the number of records returned
by the full query and the one with the LIKE '%' condition and indeed,
if I do it with WHERE dbo.tbl_pm_proj ects.SeniorMana gerID IS NULL then
I get 1290 records. 1290 + 1682 = 2972, the total number of records
in the table.

I must admit I didn't check this to start with because I'm sure this
query was returning all entries in the table previously. Is there
another wildcard or operator I can use or some config setting in the
database or the SQL server in general I can use to have it include
NULL results in queries like this?

Niall

Hi Niall,

WHERE SeniorManagerID LIKE @SearchParam
OR (SeniorManagerI D IS NULL AND @SearchParam = '%')

or

WHERE COALESCE (SeniorManagerI D, '') LIKE @SearchParam
You might also want to read this: http://www.sommarskog.se/dyn-search.html

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Niall Porter]

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<5u******* *************** **********@4ax. com>...

[snip]

WHERE SeniorManagerID LIKE @SearchParam
OR (SeniorManagerI D IS NULL AND @SearchParam = '%')

or

WHERE COALESCE (SeniorManagerI D, '') LIKE @SearchParam
Thanks Hugo, I've gone for the second option as I'm now going to have
to combine several similar queries into one, I've picked the COALESCE
option for shorter SQL code. Seems to be working nicely now, many
thanks!
You might also want to read this: http://www.sommarskog.se/dyn-search.html
Article was quite useful, again thanks. Erland, if you read this,
were you simply being modest by not pointing me at this article? Good
article all the same, I've bookmarked your site for future reference.
Best, Hugo

Thanks again all who provided responses, much appreciated.

Kind regards,
Niall

[Niall Porter]

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<5u******* *************** **********@4ax. com>...

On 6 Sep 2004 03:07:49 -0700, Niall Porter wrote:

Hi Niall,

WHERE SeniorManagerID LIKE @SearchParam
OR (SeniorManagerI D IS NULL AND @SearchParam = '%')

or

WHERE COALESCE (SeniorManagerI D, '') LIKE @SearchParam
You might also want to read this: http://www.sommarskog.se/dyn-search.html

Best, Hugo

Hi again,

For those who are interested, I discovered another way of coping with
the NULL not being the same as a zero-length entry thing, it is to do
this:

....
WHERE ISNULL(fieldYou WantToQuery, ' ') LIKE @SearchParam

making sure there IS a space between the single quotes, ISNULL used in
this manner will substitute the space character where there are NULL
values. Maybe useful if a situation arose where using COALESCE was
not appropriate for some reason.

Now the philosophical bit - if a zero-length string is not NULL, then
what IS NULL? :)

Regards,
Niall

[Hugo Kornelis]

On 7 Sep 2004 04:11:08 -0700, Niall Porter wrote:

Hugo Kornelis <hugo@pe_NO_rFa ct.in_SPAM_fo> wrote in message news:<5u******* *************** **********@4ax. com>...

On 6 Sep 2004 03:07:49 -0700, Niall Porter wrote:

Hi Niall,

WHERE SeniorManagerID LIKE @SearchParam
OR (SeniorManagerI D IS NULL AND @SearchParam = '%')

or

WHERE COALESCE (SeniorManagerI D, '') LIKE @SearchParam
You might also want to read this: http://www.sommarskog.se/dyn-search.html

Best, Hugo

Hi again,

For those who are interested, I discovered another way of coping with
the NULL not being the same as a zero-length entry thing, it is to do
this:

...
WHERE ISNULL(fieldYou WantToQuery, ' ') LIKE @SearchParam

making sure there IS a space between the single quotes, ISNULL used in
this manner will substitute the space character where there are NULL
values. Maybe useful if a situation arose where using COALESCE was
not appropriate for some reason.

Now the philosophical bit - if a zero-length string is not NULL, then
what IS NULL? :)

Regards,
Niall

Hi Niall,

ISNULL is in fact the same as COALESCE with two arguments. The only
differences are that COALESCE is extendible to three or more arguments and
it is defined in the ISO/ANSI standards for SQL; ISNULL is proprietary
Transact-SQL syntax (dating back to the time before COALESCE was
incorporated in the SQL standards).

I don't think the space between the single quotes is needed with either
ISNULL or COALESCE. In fact, a quick test shows it isn't.

For the philosophical bit: NULL is the absence of data, usually because
you don't know the data. The middle initial of William Shakespeare is the
empty (or zero-length) string, as we all know that William Shakespeare had
no middle initials. But if I ask you for the middle initial of my brother,
you'd have to answer me with NULL, as you have no way of knowing if my
brother has any middle initial and if so, what it might be.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)