CODEWITHSUNDEEP
sqlsql-servercastingsql-server-2014
Social Developer
Social Developer

Reputation: 425

SQL Cast quirkiness

I have always used CONVERT (and not CAST), as I assumed the former would recognize types and do an appropriate conversion where as the latter is simply trying to interpret a stream of bytes differently. But just learned that CAST=CONVERT for most purposes!

But can someone explain why the following happens. CAST produces different results for the same value (101), but represented differently - decimal (101) and hexadecimal (0x65) representations.

select cast(0x65 as varchar(5))
-----
e

select cast(101 as varchar(5))
-----
101

EDIT: The query was run from SSMS.

Upvotes: 2

Views: 113

Answers (2)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6566

You are trying to convert to completely different values. As Gordon mentioned, one is binary representation while the other is numeric.

But you need to note that there is some differences between CAST and CONVERT:

  1. CAST is part of the ANSI-SQL specification; whereas, CONVERT is not. In fact, CONVERT is Microsoft SQL Server implementation specific.
  2. CONVERT differences lie in that it accepts an optional style parameter which is used for formatting.

Read more here: https://www.essentialsql.com/what-is-the-difference-between-cast-and-convert/

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I assume you are using SQL Server (where the confusion between the two functions would make sense).

That is simple. 0x defines a binary constant. 101 is a numeric constant. These are not the same thing.

When you convert a binary constant to a string, it attempts to interpret the constant as a character. When you convert a number to a string, SQL Server converts the decimal representation.

You can learn more about constants in the documentation.

Upvotes: 4

Related Questions