如何搜索二进制字段？ [英] How to search binary fields?

问题描述

我想在一个包含二进制字段的表上运行查询。我如何在二元字段上过滤
？例如。其中一个字段叫做

''Account_Manager_ID''是二进制的 - 我想从公司那里做一个简单的Select *

，其中Account_Manager_ID ='''blah blah blah ''


当我这样做时，它不返回任何数据。我如何绕过这个？


谢谢！

Hi,

I want to run queries on a table that has binary fields in it. How do I
filter on a binary field? E.g. One of the fields is called
''Account_Manager_ID'' which is binary - I would like to do a simple Select *
from company where Account_Manager_ID = ''blah blah blah''

When I do this, it returns no data. How do I get round this?

Thanks!

推荐答案

Mintyman（mi ***** *@ntlworld.com）写道：

Mintyman (mi******@ntlworld.com) writes:

我想在一个包含二进制字段的表上运行查询。我如何在二元字段上过滤
？例如。其中一个字段叫做

''Account_Manager_ID''是二进制的 - 我想做一个简单的

选择*来自公司，其中Account_Manager_ID =''blah blah blah ''


当我这样做时，它不返回任何数据。我怎么绕这个？

I want to run queries on a table that has binary fields in it. How do I
filter on a binary field? E.g. One of the fields is called
''Account_Manager_ID'' which is binary - I would like to do a simple
Select * from company where Account_Manager_ID = ''blah blah blah''

When I do this, it returns no data. How do I get round this?

WHERE Account_Manager_ID = convert（varbinary，''blah blah blah''）


是我从你的猜测描述。


但是如果你发表表格的CREATE TABLE语句，一些示例数据和一个实际查询，它可能会更好。这将减少猜测的数额。

-

Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se


SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro .. .ads / books.mspx

SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx

WHERE Account_Manager_ID = convert(varbinary, ''blah blah blah'')

is what I woudl guess from your description.

But it''s probably better if you post the CREATE TABLE statement for
the table, some sample data and an actual query. That would reduce the
amount of guessing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

我尝试了你的建议，但它仍然没有返回任何结果！


这是你要求的信息：


如果存在（ select * from dbo.sysobjects，其中id =

object_id（N''[dbo]。[_ COMPANY]''）和OBJECTPROPERTY（id，N''IsView''）= 1）

drop view [dbo]。[_ COMPANY]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

创建视图dbo._COMPANY

AS

SELECT dbo.Company.Company_Id，dbo.Company.Rn_Descriptor，

dbo。 Company.Rn_Create_Date，dbo.Company.Rn_Create_User，

dbo.Company.Rn_Edit_Date，

dbo.Company.Rn_Edit_User，dbo.Company.Company_Name，

dbo.Company.Address_1，dbo.Company.Address_2，dbo.Company.Address_3，

dbo.Company.Country，dbo.Company.Zip，

dbo。 Company.Phone，dbo.Company.Fax，dbo.Company.WWW，

dbo.Company.Industry_Type，

dbo.Company.State_，dbo.Company.City，

dbo.Company.Lead_Source_Id，dbo.Company.Account_Code，

dbo.Company.Business_Unit，

dbo.Company.Account_Manager_Id，

dbo.Company.Lead_Source_Type，dbo.Territory.Territory_Name，

dbo.Territory.Account_Manager_Id AS Expr1，

dbo.Material.Material_Name

FROM dbo.Company INNER JOIN

dbo.Territory ON dbo.Company.Territory_Id =

dbo.Territory.Territory_Id INNER JOIN

dbo.Material__Bridge ON dbo.Company.Company_Id =

dbo.Material__Bridge.To_Company INNER JOIN

dbo.Material ON dbo.Material__Bridge.From_Materials =

dbo.Material .Material_Id


GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO


一些样本数据：

0x00C0000000003B86应用陶瓷公司，弗里蒙特 - +1（510）

249-9700 2001-08-21 05：29：15.453 0x0000000000000105 2006-08-02 12：39：42.923

0x00000000000001A6 Applied Ceramics Incorporated 48630 Milmont Drive NULL

NULL美国94538 [phone] NULL appliedceramics.com NULL CA

Fremont NULL NULL NULL 0x00E0000000000003 NULL US - Logitech - West

0x00E0000000000003 Aluminiumoxide

0x00C0000000003B8 6应用陶瓷公司，弗里蒙特 - +1（510）

249-9700 2001-08-21 05：29：15.453 0x0000000000000105 2006-08-02 12：39：42.923

0x00000000000001A6 Applied Ceramics Incorporated 48630 Milmont Drive NULL

NULL美国94538 [phone] NULL appliedceramics.com NULL CA

Fremont NULL NULL NULL 0x00E0000000000003 NULL美国 - Logitech - West

0x00E0000000000003氮化硅

0x00C000000000481C Spectrum Petrographics，Winston - [phone]

2001-08- 21 05：34：02.687 0x0000000000000105 2006-03-01 09：49：33.050

0x00000000000000D6 Spectrum Petrographics 499 Dillard Gardens Rd NULL NULL

美国97496 [phone] NULL NULL NULL或Winston NULL NULL

NULL 0x00E0000000000003 NULL US - Logitech - West 0x00E0000000000003 Ores

0x00C000000000686C Lumenyte International，Irvine - [phone]

2003-06-19 13：12：13.877 0x00C000000000001B 2006-01-27 11 ：43：05.553

0x00000000000000D6 Lumenyte International 12 Whatney NULL NULL美国

92618 [phone] [phone] NULL NULL CA Irvine NULL NULL NULL

0x00E0000000000003 NULL US - Logitech - West 0x00E0000000000003 Acrylic

0x00C00000000068C3 Lawrence Livermore National Labs x，L-250，Livermore - +1

[phone] 2003-06-19 13：12：13.877 0x00C000000000001B 2006-01-27

11：39：49.550 0x00000000000000D6 Lawrence Livermore National Labs x PO Box

808 NULL NULL美国94550 [phone] [phone] NULL

NULL CA Livermore NULL NULL NULL 0x00E0000000000003 NULL US - Logitech -

West 0x00E0000000000003 ADP

我正在使用的查询是：


从_COMPANY中选择*，其中Company_Id = convert（varbinary，

''0x00C0000000003B86''）

希望有所帮助！

I tried your suggestion but it still returns no results!

Here is the information you requested:

if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[_COMPANY]'') and OBJECTPROPERTY(id, N''IsView'') = 1)
drop view [dbo].[_COMPANY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo._COMPANY
AS
SELECT dbo.Company.Company_Id, dbo.Company.Rn_Descriptor,
dbo.Company.Rn_Create_Date, dbo.Company.Rn_Create_User,
dbo.Company.Rn_Edit_Date,
dbo.Company.Rn_Edit_User, dbo.Company.Company_Name,
dbo.Company.Address_1, dbo.Company.Address_2, dbo.Company.Address_3,
dbo.Company.Country, dbo.Company.Zip,
dbo.Company.Phone, dbo.Company.Fax, dbo.Company.WWW,
dbo.Company.Industry_Type,
dbo.Company.State_, dbo.Company.City,
dbo.Company.Lead_Source_Id, dbo.Company.Account_Code,
dbo.Company.Business_Unit,
dbo.Company.Account_Manager_Id,
dbo.Company.Lead_Source_Type, dbo.Territory.Territory_Name,
dbo.Territory.Account_Manager_Id AS Expr1,
dbo.Material.Material_Name
FROM dbo.Company INNER JOIN
dbo.Territory ON dbo.Company.Territory_Id =
dbo.Territory.Territory_Id INNER JOIN
dbo.Material__Bridge ON dbo.Company.Company_Id =
dbo.Material__Bridge.To_Company INNER JOIN
dbo.Material ON dbo.Material__Bridge.From_Materials =
dbo.Material.Material_Id

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Some sample data:

0x00C0000000003B86 Applied Ceramics Incorporated, Fremont - +1 (510)
249-9700 2001-08-21 05:29:15.453 0x0000000000000105 2006-08-02 12:39:42.923
0x00000000000001A6 Applied Ceramics Incorporated 48630 Milmont Drive NULL
NULL United States 94538 [phone] NULL appliedceramics.com NULL CA
Fremont NULL NULL NULL 0x00E0000000000003 NULL US - Logitech - West
0x00E0000000000003 Aluminiumoxide
0x00C0000000003B86 Applied Ceramics Incorporated, Fremont - +1 (510)
249-9700 2001-08-21 05:29:15.453 0x0000000000000105 2006-08-02 12:39:42.923
0x00000000000001A6 Applied Ceramics Incorporated 48630 Milmont Drive NULL
NULL United States 94538 [phone] NULL appliedceramics.com NULL CA
Fremont NULL NULL NULL 0x00E0000000000003 NULL US - Logitech - West
0x00E0000000000003 Silicon Nitride
0x00C000000000481C Spectrum Petrographics, Winston - [phone]
2001-08-21 05:34:02.687 0x0000000000000105 2006-03-01 09:49:33.050
0x00000000000000D6 Spectrum Petrographics 499 Dillard Gardens Rd NULL NULL
United States 97496 [phone] NULL NULL NULL OR Winston NULL NULL
NULL 0x00E0000000000003 NULL US - Logitech - West 0x00E0000000000003 Ores
0x00C000000000686C Lumenyte International, Irvine - [phone]
2003-06-19 13:12:13.877 0x00C000000000001B 2006-01-27 11:43:05.553
0x00000000000000D6 Lumenyte International 12 Whatney NULL NULL United States
92618 [phone] [phone] NULL NULL CA Irvine NULL NULL NULL
0x00E0000000000003 NULL US - Logitech - West 0x00E0000000000003 Acrylic
0x00C00000000068C3 Lawrence Livermore National Labs x, L-250, Livermore - +1
[phone] 2003-06-19 13:12:13.877 0x00C000000000001B 2006-01-27
11:39:49.550 0x00000000000000D6 Lawrence Livermore National Labs x PO Box
808 NULL NULL United States 94550 [phone] [phone] NULL
NULL CA Livermore NULL NULL NULL 0x00E0000000000003 NULL US - Logitech -
West 0x00E0000000000003 ADP
The query i''m using is:

select * from _COMPANY where Company_Id = convert(varbinary,
''0x00C0000000003B86'')

Hope that helps!

Mintyman（mi ****** @ ntlworld.com）wri tes：

Mintyman (mi******@ntlworld.com) writes:

我正在使用的查询是：


select * from _COMPANY where Company_Id = convert（varbinary，

''0x00C0000000003B86''）

The query i''m using is:

select * from _COMPANY where Company_Id = convert(varbinary,
''0x00C0000000003B86'')

我本来希望看到基础表定义，是

确定数据类型是什么。但假设它是二进制的，这应该是
工作：


从_COMPANY中选择*，其中Company_Id = 0x00C0000000003B86


-

Erland Sommarskog，SQL Server MVP， es****@sommarskog.se


SQL Server 2005联机丛书
http://www.microsoft.com/technet/pro...ads/books.mspx

SQL Server联机丛书2000年在
http://www.microsoft。 com / sql / prodinf ... ons / books.mspx

I would have preferred to see the underlying table definition, to be
sure what the data type is. But assuming that it is binary, this should
work:

select * from _COMPANY where Company_Id = 0x00C0000000003B86

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

这篇关于如何搜索二进制字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！