No results using varbinary datatype in where clause

• bopeavy

  SSCertifiable

  Points: 5869

  More actions

  June 2, 2011 at 10:20 am

  #239148

  I am running a query against a table that I know has the value in the table but get no results.

  The Query:

  SELECT TOP 1000 [account_sid] ,[account_name] ,[isuser]

  FROM [Test].[dbo].[cache]

  where [account_sid] = '0x01050000000000051500000078006D1F43170A3207E53B2B992B0000'

  example of table:

  account_sid account_name isuser

  0x01050000000000051500000078006D1F43170A3207E53B2B99280000RS\SECSTeam0

  0x01050000000000051500000078006D1F43170A3207E53B2B992B0000RS\bpvy 1

  0x01050000000000051500000078006D1F43170A3207E53B2B9A2B0000RS\rpda 1

  Clearly the second row contains the value but I get no results any thoughts as to why?

  :cool:

  
  

  "There are no problems! Only solutions that have yet to be discovered!"

• GSquared

  SSC Guru

  Points: 260824

  More actions

  June 2, 2011 at 10:28 am

  #1333225

  Try it without the quotation marks.

  CREATE TABLE #T (

  C1 VARBINARY(10));

  INSERT INTO #T (C1)

  VALUES (0x1234),(0x4567);

  SELECT *

  FROM #T

  WHERE C1 = '0x1234';

  SELECT *

  FROM #T

  WHERE C1 = 0x1234;

  The first one will return 0 rows, the second one will return the desired row.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• bopeavy

  SSCertifiable

  Points: 5869

  More actions

  June 2, 2011 at 10:41 am

  #1333234

  Hey appreciate that sometimes all you need is a second pair of eyes!

  :cool:

  
  

  "There are no problems! Only solutions that have yet to be discovered!"

• megha12megha

  SSC Enthusiast

  Points: 120

  More actions

  February 17, 2017 at 7:46 am

  #1929112

  GSquared - Thursday, June 2, 2011 10:28 AM

  Try it without the quotation marks.CREATE TABLE #T (C1 VARBINARY(10));INSERT INTO #T (C1)VALUES (0x1234),(0x4567);SELECT *FROM #TWHERE C1 = '0x1234';SELECT *FROM #TWHERE C1 = 0x1234;The first one will return 0 rows, the second one will return the desired row.

  I can get 1 value but not 0
  how Can I get 0 as well in this query?

• megha12megha

  SSC Enthusiast

  Points: 120

  More actions

  February 17, 2017 at 7:46 am

  #1929113

  GSquared - Thursday, June 2, 2011 10:28 AM

  Try it without the quotation marks.CREATE TABLE #T (C1 VARBINARY(10));INSERT INTO #T (C1)VALUES (0x1234),(0x4567);SELECT *FROM #TWHERE C1 = '0x1234';SELECT *FROM #TWHERE C1 = 0x1234;The first one will return 0 rows, the second one will return the desired row.

  I can get 1 value but not 0
  how Can I get 0 as well in this query?

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  February 17, 2017 at 11:39 am

  #1929178

  megha12megha - Friday, February 17, 2017 7:46 AM

  GSquared - Thursday, June 2, 2011 10:28 AM

  Try it without the quotation marks.CREATE TABLE #T (C1 VARBINARY(10));INSERT INTO #T (C1)VALUES (0x1234),(0x4567);SELECT *FROM #TWHERE C1 = '0x1234';SELECT *FROM #TWHERE C1 = 0x1234;The first one will return 0 rows, the second one will return the desired row.

  I can get 1 value but not 0
  how Can I get 0 as well in this query?

  Not entirely sure whether you mean a 0 value (aka 0x0), or 0 records...   Anyway, take a look at the following:
  
CREATE TABLE #T (
    C1 varbinary(10)
);

  INSERT INTO #T (C1)
VALUES    (0x1234),
        (0x4567),
        (0x0);

  SELECT *
FROM #T
WHERE C1 = '0x1234';

  SELECT *
FROM #T
WHERE C1 = 0x1234;

  SELECT *
FROM #T
WHERE C1 IN (0x0, 0x1234);

  DROP TABLE #T;


  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)