June 2, 2011 at 10:20 am
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?
June 2, 2011 at 10:28 am
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
June 2, 2011 at 10:41 am
Hey appreciate that sometimes all you need is a second pair of eyes!
February 17, 2017 at 7:46 am
GSquared - Thursday, June 2, 2011 10:28 AMTry 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?
February 17, 2017 at 7:46 am
GSquared - Thursday, June 2, 2011 10:28 AMTry 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?
February 17, 2017 at 11:39 am
megha12megha - Friday, February 17, 2017 7:46 AMGSquared - Thursday, June 2, 2011 10:28 AMTry 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)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply