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_projects.SeniorManagerID 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*********@yahoo.co.uk> wrote in message
news:2d**************************@posting.google.c om...

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_projects.SeniorManagerID 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*********@yahoo.co.uk> wrote in message
news:2d**************************@posting.google.c om...

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_projects.SeniorManagerID 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_projects.SeniorManagerID is NULL
....
and see if it returns anything.

[Erland Sommarskog]

Niall Porter (ni*********@yahoo.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_projects.SeniorManagerID 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****@sommarskog.se

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

[Ross Presser]

ni*********@yahoo.co.uk (Niall Porter) wrote in
news:2d**************************@posting.google.c om:

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_projects.SeniorManagerID 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_projects.SeniorManagerID 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*********@yahoo.co.uk (Niall Porter) wrote in message news:<2d**************************@posting.google. 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_projects.SeniorManagerID 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_projects.SeniorManagerID 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 (SeniorManagerID IS NULL AND @SearchParam = '%')

or

WHERE COALESCE (SeniorManagerID, '') 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_rFact.in_SPAM_fo> wrote in message news:<5u********************************@4ax.com>. ..

[snip]

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

or

WHERE COALESCE (SeniorManagerID, '') 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_rFact.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 (SeniorManagerID IS NULL AND @SearchParam = '%')

or

WHERE COALESCE (SeniorManagerID, '') 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(fieldYouWantToQuery, ' ') 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_rFact.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 (SeniorManagerID IS NULL AND @SearchParam = '%')

or

WHERE COALESCE (SeniorManagerID, '') 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(fieldYouWantToQuery, ' ') 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)

[Ross Presser]

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in
news:ov********************************@4ax.com:

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.

Or if you even have a brother. :)

[dara burke]

Hi,

I had a problem similar to the one mentioned above, but now
unfortunately I have a different (but related one).
I am using Access 2000 on apache and I seem to be limited in exactly
what I can do to work my way around the NULL/empty string dilemma.
Access doesnt support COALESCE, doesn't support nested statements (as
far as I can work out) and it doesn't support overloading the ISNULL
function (only has the single parameter version).
Now I know that i'd probably be better off upgrading but i'd still like
to find the work around for a simple static sql statement that looks
something like this.

SELECT Name, Size, Age, Keyword
FROM Gdatabase
WHERE Gdatabase.Name LIKE '$name'
OR Gdatabase.Size LIKE '$size'
AND Gdatabase.Age LIKE '$age'
AND Gdatabase.Keyword LIKE '$keyword'

suggested solution ...

SELECT ...
FROM ...
WHERE ...
OR ...
AND ISNULL(Gdatabase.Size,' ') LIKE '$size'
AND ISNULL(Gdatabase.Age,' ') LIKE '$age'
...

gives me driver error ... wrong number of arguments used in function.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ross Presser]

On 14 Oct 2004 04:28:39 GMT, dara burke wrote:

AND ISNULL(Gdatabase.Age,' ') LIKE '$age'
..

gives me driver error ... wrong number of arguments used in function.

In Access, ISNULL() is a VBA function that returns true if its single
argument is null. Try

IIF(ISNULL(Gdatabase.Age),' ',Gdatabase.Age)

That's how it would work from inside an actual Access database running in
the access program; I am not sure how Apache will respond.