How to search binary fields?

[Mintyman]

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!

[Erland Sommarskog]

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

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')

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

[Mintyman]

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!

[Erland Sommarskog]

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

The query i'm using is:

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

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

[--CELKO--]

>I want to run queries on a table that has binary fields [sic] in it.<<

Fields and columns are totally different concepts. If you knew that,
you would not use low-level physical concepts like a binary field in an
SQL database. SQL is based on higher level abstractions. It also uses
a concept called "Normal Forms" that you need to learn; that means that
every column is a scalar value. That means that the bits in the
physical implementation have no meaning in themselves.

>How do I filter on a binary field? <<

With propriertary, hardware dependent (high end or low end? ones or
twos complement math? how big is a word? etc.) kludges or with
unreadable math expressions, like all bad programmers!

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

Fields and columns are totally different concepts.

For people with low experience of SQL, I should clarify what Joe Celko
is talking about: "Fields" starts with "F", columns starts with "C".

SQL people normally use "rows" and "columns", but we understand "records"
and "fields" without problem. It can even happen that we use them ourselves.
Even Celko has been caught doing it.

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

[--CELKO--]

>SQL people normally use "rows" and "columns", but we understand "records"and "fields" without problem. <<

NO. NO, NO! Newbies -- not SQL people -- use "records"and "fields"
because they are still thinking in file systems terms and not RDBMS.
They keep thinking in terms of file systems and not RDBMS. Trust me;
I teach these people how to program in SQL.

[Ed Murphy]

--CELKO-- wrote:

>>SQL people normally use "rows" and "columns", but we understand "records"and "fields" without problem. <<

NO. NO, NO! Newbies -- not SQL people -- use "records"and "fields"
because they are still thinking in file systems terms and not RDBMS.
They keep thinking in terms of file systems and not RDBMS. Trust me;
I teach these people how to program in SQL.

It'd help if you would explain what "thinking in file systems terms"
and "thinking in RDBMS terms" actually mean (for records/rows and for
fields/columns). Writing it once and subsequently giving out the URL
would likely be the most efficient approach.

Without such an explanation, newbies are prone to think "oh, this guy
is just some kook obsessed with unimportant jargon" and dismiss it all.

[Erland Sommarskog]

Ed Murphy (em*******@socal.rr.com) writes:

--CELKO-- wrote:

>>>SQL people normally use "rows" and "columns", but we understand
"records"and "fields" without problem. <<

NO. NO, NO! Newbies -- not SQL people -- use "records"and "fields"
because they are still thinking in file systems terms and not RDBMS.
They keep thinking in terms of file systems and not RDBMS. Trust me;
I teach these people how to program in SQL.

It'd help if you would explain what "thinking in file systems terms"
and "thinking in RDBMS terms" actually mean (for records/rows and for
fields/columns). Writing it once and subsequently giving out the URL
would likely be the most efficient approach.

Without such an explanation, newbies are prone to think "oh, this guy
is just some kook obsessed with unimportant jargon" and dismiss it all.

Ah, but that what it is! We used to called it subroutines or procedures.
Then Smalltalk came along and now its all "methods".

As for SQL people not using "records" or "fields", I have a very trust-
worthy testimony reporting that Celko said "record" in a conferernce
presentation a year ago. But maybe that's why he keeps nagging this
point: to remind himself.

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

[--CELKO--]

>>It'd help if you would explain what "thinking in file systems terms" and "thinking in RDBMS terms" actually mean (for records/rows and for fields/columns). <<

I have posted details, I wrote a book on SQL PROGRAMMING STYLE and I am
working on my seventh book now under title "Thinking in Sets" which
deals with table abd data driven solutions versus procedural ones.

> Writing it once and subsequently giving out the URL would likely be the most efficient approach. <<

One of my resolutions is to start taking care of my website instead of
spending so much time in newsgroups. Probably put something there ..

[Mintyman]

Hi Erland,

Apologies for the delay in responding. I have been on Christmas break.

Thanks for helping me out with this one!

Mintyman

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn*********************@127.0.0.1...

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

>The query i'm using is:

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

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

[Shuurai]

>

NO. NO, NO! Newbies -- not SQL people -- use "records"and "fields"
because they are still thinking in file systems terms and not RDBMS.
They keep thinking in terms of file systems and not RDBMS. Trust me;
I teach these people how to program in SQL.

Funny - I was just browsing through "The Guru's Guide to Transact-SQL"
by Ken Henderson the other day... I'm assuming that you're familiar
with both the author and his book, seeing as how you wrote the
introduction. Anyway, Ken writes in the first chapter that he will be
using rows/records and columns/fields interchangeably throughout the
book.

Do you consider Ken a newbie then? Does he not qualify as "SQL people"
in your estimation? Because you certainly implied otherwise in your
intro to his book.

The fact of the matter is, Erland is absolutely correct. People who
use SQL use these terms interchangeably. The people who make a big
deal of it are akin to the people who correct grammar errors to win an
argument. If they had something useful to say, they would.

[--CELKO--]

>Ken writes in the first chapter that he will be using rows/records and columns/fields interchangeably throughout the book. <<

Hopefully that will be fixed in his SQL-2005 book. Since the
Sybase/SQL Server family is built on a file system model (i.e. static
contigous physical storage) it is not such a difference here. But in
Sand, Teradata, Model 204 and other SQL products they use totally
different storage methods. For example, Sand outputs table one column
at a time, not one row at a time. They are assembled from compressed
bit vectors and are not contigous.

> People who use SQL use these terms interchangeably. <<

No, we don't; SQL Server people and Newbies who are still thinking in
terms of file systems do.

>The people who make a big deal of it are akin to the people who correct grammar errors to win an argument. <<

No, more like accountants, mathematicians or engineers who insist on
the proper terms, computations and mindsets to solve problems. Or, to
keep you analogy, more like editors and writers who who correct grammar
errors to do their job properly.

> If they had something useful to say, they would. <<

Gee, after seven good selling books, 800 artifcles and having a few
standard SQL programming techniques named after me, I think I can claim
having had something useful to say :)

[Shuurai]

--CELKO-- wrote:

Ken writes in the first chapter that he will be using rows/records and columns/fields interchangeably throughout the book. <<

Hopefully that will be fixed in his SQL-2005 book.

It doesn't need to be fixed - as he rightly points out, it is common
usage among those in the industry.

Since the
Sybase/SQL Server family is built on a file system model (i.e. static
contigous physical storage) it is not such a difference here. But in
Sand, Teradata, Model 204 and other SQL products they use totally
different storage methods. For example, Sand outputs table one column
at a time, not one row at a time. They are assembled from compressed
bit vectors and are not contigous.

The terminology one uses in casual conversation does not need to match
the inner workings of the technology.

People who use SQL use these terms interchangeably. <<

No, we don't; SQL Server people and Newbies who are still thinking in
terms of file systems do.

Yes, we do; if you spent some time with real developers and
administrators, you'd find that the terms "record" and "field" are
very, very commonly used. Personally, I prefer row and column but so
long as you know what people are saying, it's just not an issue.

The people who make a big deal of it are akin to the people who correct grammar errors to win an argument. <<

No, more like accountants, mathematicians or engineers who insist on
the proper terms, computations and mindsets to solve problems.

I've worked with engineers and accountants for years. The ones who
nitpick over syntax are typically the ones that like to nitpick in
general.

Or, to keep you analogy, more like editors and writers who who correct grammar
errors to do their job properly.

A good editor knows that common use of the language by the intended
audience is every bit as important as formal rules of grammar.

If they had something useful to say, they would. <<

Gee, after seven good selling books, 800 artifcles and having a few
standard SQL programming techniques named after me, I think I can claim
having had something useful to say :)

Which makes one wonder why it doesn't happen more often. :)

[Ed Murphy]

Shuurai wrote:

--CELKO-- wrote:

>>>The people who make a big deal of it are akin to the people who correct grammar errors to win an argument. <<

No, more like accountants, mathematicians or engineers who insist on
the proper terms, computations and mindsets to solve problems.

I've worked with engineers and accountants for years. The ones who
nitpick over syntax are typically the ones that like to nitpick in
general.

Insisting on the proper mindset is appropriate, as the improper mindset
leads to e.g. use of cursors instead of joins. The thing with Celko is
that he frequently jumps on syntax, sometimes implies that there are
underlying mindset issues, and occasionally explains what those issues
actually are. :)