Convert Binary Hex representation to string keeping same output

Question

I have the following SQL Server query

DECLARE @StartRange BINARY(8)
DECLARE @EndRange BINARY(8)

SET @StartRange = 0x0000004A50F119B
SET @EndRange = 0x00000004A50F11FF 

SELECT 
    CONVERT(BINARY(6), RW + CONVERT(INTEGER, @StartRange))
FROM
    (SELECT
         ROW_NUMBER() OVER (ORDER BY a.id) AS RW 
     FROM
         syscolumns, syscolumns a, syscolumns b, syscolumns c) b
WHERE
     RW BETWEEN 1 AND (CONVERT(INTEGER, @EndRange) - CONVERT(INTEGER, @StartRange))

This generates 100 records like following one:

0xFFFFA50F119C

What I need is to convert the hex binary representation to string so I can remove the 0x value and just keep the next 12 characters. So what I need is to get something like this:

FFFFA50F119C

If I do a cast or convert it will try to convert that into ASCII characters and that's not what I need.

Any clue?

Dale K · Accepted Answer · 2019-12-09 20:36:02Z

2

convert to a string then use substring to remove the 0x. convert has a special style for converting binary data to a string represented in hex.

substring(convert(varchar(12),CONVERT(BINARY(6), RW + CONVERT(INTEGER, @StartRange)),2/*Binary convert style*/),3,10)

edited Dec 9, 2019 at 20:36

answered Dec 9, 2019 at 20:26

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert Binary Hex representation to string keeping same output

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related