?
Solved

select where  image data type  is null

Posted on 2007-10-05
7
Medium Priority
?
804 Views
Last Modified: 2010-03-19
What's the syntax for testing for image data type of null in a select where statement ?

When i execute a query in sql server it has 0x00 , which i believe is binary zero,

complete novice so you might need a bit of patience with me

thanks

Steve
0
Comment
Question by:stevejebson
  • 5
  • 2
7 Comments
 
LVL 8

Expert Comment

by:MrRobot
ID: 20025675
if myVar is null
  print 'myVar is null'
0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20025682
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types on sql server books online.

0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20025686
ah, a 'select where' statement, then

select ID from myTable where myImage is null

=)
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 1

Author Comment

by:stevejebson
ID: 20025889
Except that doesn't return anything !

when i run a query that selects all and see the results in the results pane there's an 0x00 in the column, how do i test for that 'cos it appears that's not a Null but is it Binasry zero ?? if so how do i test against that ? when i select id where myImage=0 it gives me an error saying it can't test umage against tinyint
0
 
LVL 8

Accepted Solution

by:
MrRobot earned 2000 total points
ID: 20025915
in sql server 2005, convert it to a varbinary(max), then you can test it against values.

ntext, text, and image data types store the data in a different way, so many functions won't work on them since the sql server needs different methods to retrieve data from these type of columns.

0
 
LVL 8

Expert Comment

by:MrRobot
ID: 20025937
after converting,

select count(*) from myTable where ImageColumn = cast(0 as varbinary(max))
0
 
LVL 1

Author Comment

by:stevejebson
ID: 20025948
thanks, id di it the other way roun...Where Cast( [SignatureOfClient] as varbinary(max)) = 0

and thanks for other info, unfortuantely it's a db i've inherited so i can't go changing formats just yet.

thanks again.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question