bintostr

Description

Converts a sequence of hexadecimal digits to a string of its equivalent alphanumeric characters or varbinary data.

Syntax

select bintostr(sequence of hexadecimal digits)

Parameters

sequence of hexadecimal digits: is the sequence of valid hexadecimal digits, consisting of [0 – 9], [a – f] and [A – F], and which is prefixed with “0x”.

Examples

Example 1

Converts the hexadecimal sequence of “0x723ad82fe” to an alphanumeric string of the same value:

1> select bintostr(0x723ad82fe)
2> go

-----------------------------------
0723ad82fe

In this example, the in-memory representation of the sequence of hexadecimal digits and its equivalent alphanumeric character string are:

Hexadecimal digits (5 bytes)
0	7	2	3	a	d	8	2	f	e
Alphanumeric character string (9 bytes)
0		7		2		3		a		d	8	2	f	e

The function processes hexadecimal digits from right to left. In this example, the number of digits in the input is odd. For this reason, the alphanumeric character sequence has a prefix of “0” and is reflected in the output.

Example 2

Converts the hexadecimal digits of a local variable called @bin_data to an alphanumeric string equivalent to the value of “723ad82fe”:

declare @bin_data varchar(30)
select @bin_data = 0x723ad82fe
select bintostr(@bin_data)
go

----------
0723ad82fe

Usage

Any invalid characters in the input results in null as the output.
The input must be valid varbinary data.
A NULL input results in NULL output.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute bintostr.