在varchar字段上使用LIKE'％'时，通配符不匹配，很奇怪！ [英] Wildcard doesn't match using LIKE '%' on a varchar field, wierd!

问题描述

这肯定是一个微不足道的麻烦......？


我正在使用SP3运行SQL 2k标准。我有一张桌子，我正在尝试

在varchar字段上使用LIKE运算符进行查询，如下所示


....

WHERE dbo.tbl_pm_projects.SeniorManagerID LIKE''％''

....


实际上％传入了当用户

选择所有经理时的申请从下拉列表中选择要过滤的

管理器。如果他们从列表中选择一个经理的名字

就会变成LIKE''ajames''或者其他什么。


但是 - 该表目前包含2972记录。如果我取出

WHERE子句，SELECT会返回所有记录 - 很好 - 但如果我把

where子句放入其中只返回1682 !!我认为％的意思是

匹配，我在这里引用SQL服务器联机丛书文件; 任何字符串

，零个或多个字符。有人向我解释这里有什么？
这里？


TIA

Niall

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

推荐答案

肯定会搞砸......？


我正在使用SP3运行SQL 2k标准。我有一张桌子，我正在尝试

在varchar字段上使用LIKE运算符进行查询，如下所示


....

WHERE dbo.tbl_pm_projects.SeniorManagerID LIKE''％''

....


实际上％传入了当用户

选择所有经理时的申请从下拉列表中选择要过滤的

管理器。如果他们从列表中选择一个经理的名字

就会变成LIKE''ajames''或者其他什么。


但是 - 该表目前包含2972记录。如果我取出

WHERE子句，SELECT会返回所有记录 - 很好 - 但如果我把

where子句放入其中只返回1682 !!我认为％的意思是

匹配，我在这里引用SQL服务器联机丛书文件; 任何字符串

，零个或多个字符。有人向我解释这里有什么？
在这里？


TIA

Niall

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

你可能已经看过这个......我只是想知道是否

那里有一个模式，它没有返回的东西？


如果你从''完整''

记录集中的''like'记录中减去你的结果，那么在SeniorManagerID字段中是否有任何东西可以用来支持

模式？


" Niall Porter" < NI ********* @ yahoo.co.uk>在消息中写道

新闻：2d ************************** @ posting.google.c om ...

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...

这肯定是Micro

当然搞砸......？

我正在运行SQL 2k标准SP3。我有一个表，我正在尝试使用varchar字段上的LIKE运算符进行查询，如下所示

...
WHERE dbo.tbl_pm_projects.SeniorManagerID LIKE'' ％''
...

实际上，当用户选择所有经理时，应用程序会传入％。从用于选择要过滤的
经理的下拉列表中。如果他们从列表中选择一个经理的名字
就会变成LIKE''ajames''或者其他什么。

但是 - 该表目前包含2972条记录。如果我取出
WHERE子句，SELECT返回所有记录 - 很好 - 但如果我把
where子句放入其中只返回1682 !!我认为％是为了匹配，我在这里引用SQL服务器联机丛书文件; 任何字符串，包含零个或多个字符。有人向我解释这里有什么？

TIA
Niall

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

这篇关于在varchar字段上使用LIKE'％'时，通配符不匹配，很奇怪！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！