SQL teaser/task: proof that these are the same values

[SQLDenis]

Can you write code that proofs that @c and @b are the same value (0x1F40B33B42750DA9BBCD79F36728C7C9 )

declare @c varchar(34), 
@b varbinary(16) 


set @c = '0x1F40B33B42750DA9BBCD79F36728C7C9' 
set @b = 0x1F40B33B42750DA9BBCD79F36728C7C9

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

2005 only (but I bet you could create the function in 2000)

Spoiler

select case when @c = sys.fn_varbintohexstr(@b) then 'True' else 'False' end

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[SQLDenis]

what about a 2000 version?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

Maybe later ;-)

[small]----signature below----[/small]
I can't compete with you physically, and you're no match for my brains.
You're that smart?
Let me put it this way. Have you ever heard of Plato, Aristotle, Socrates?
Yes.
Morons!

 

[SQLDenis]

how 'bout a little cheating?

sp_helptext 'fn_varbintohexstr'
and
sp_helptext 'fn_varbintohexsubstring'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

How about undocumented system functions?

Spoiler

declare @c varchar(34),
@b varbinary(16)

set @c = '0x1F40B33B42750DA9BBCD79F36728C7C9'
set @b = 0x1F40B33B42750DA9BBCD79F36728C7C9

Declare @str varchar(200)

Exec master..xp_varbintohexstr @b, @str OUT

If @str = @c
Select 'Equal'
Else
Select 'Not Equal'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

Spoiler

I can't write (correct) code to prove they are the same value because they aren't the same value. One is a string and the other is varbinary (a different number of bytes, too mind you). They can be converted to a different representation that matches each other, but they are no more the same value than '7' and 7 are. On the other hand, '7' and 0x37 ARE the same value, in every collation that I am familiar with (but maybe not in some I don't know about).

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]