# Remove 0x from binary values using t-sql

• ### Pergunta

• Hi All,

How do I remove 0x from a binary value using t-sql.

Input is 0x000000000000048B

I need output to be 000000000000048B

Please can anybody send me the t-sql function to convert like this.

Thanks,

RH

sql
terça-feira, 29 de novembro de 2011 16:40

### Todas as Respostas

• Check out the following solution:

```DECLARE @hex varchar(max)=
master.dbo.fn_varbintohexstr(convert(varbinary(max), 0x000000000000048B))
SELECT RIGHT(@hex,len(@hex)-2)
-- 000000000000048b
```

The function in the solution is undocumented.

Related article:

http://blogs.msdn.com/b/sqltips/archive/2008/07/02/converting-from-hex-string-to-varbinary-and-vice-versa.aspx

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
• Editado terça-feira, 29 de novembro de 2011 16:49
terça-feira, 29 de novembro de 2011 16:47
• ```declare @k binary(8)

set @k = 0x000000000000048B

select convert(varchar, @k, 2)
```

• Sugerido como Resposta terça-feira, 29 de novembro de 2011 17:50
• Não Sugerido como Resposta domingo, 4 de dezembro de 2011 22:02
• Sugerido como Resposta sábado, 28 de março de 2020 07:05
terça-feira, 29 de novembro de 2011 16:58
• And here i thought CONVERT() was only for dates.

It's amazing what you can learn when re-reading the documentation.

terça-feira, 29 de novembro de 2011 17:52
• Brian,

The CONVERT solution is not robust:

```declare @k binary(20)
set @k = 0x112233445566778899aabbccddeeff11223344
select convert(varchar, @k, 2)
-- 112233445566778899AABBCCDDEEFF
```

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
domingo, 4 de dezembro de 2011 22:04
• Brian,

The CONVERT solution is not robust:

```declare @k binary(20)
set @k = 0x112233445566778899aabbccddeeff11223344
select convert(varchar, @k, 2)
-- 112233445566778899AABBCCDDEEFF
```

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

HI SQLUSA !

Your observations are not correct, i did lil more reseaarch on this and here are my findings;

```DECLARE @myvarchar VARCHAR(MAX),@myvarbinary VARBINARY(MAX)
SET @myvarchar = '112233445566778899aabbccddeeff11223344'
SET @myvarbinary = CAST(@myvarchar AS VARBINARY(MAX))
SELECT @myvarchar AS MyVarchar,@myvarbinary AS MyVarbinary, CAST(@myvarbinary AS VARCHAR(MAX)) AS VarcharValue
--112233445566778899aabbccddeeff11223344 0x3131323233333434353536363737383839396161626263636464656566663131323233333434 112233445566778899aabbccddeeff11223344
SELECT @myvarchar AS MyVarchar,@myvarbinary AS MyVarbinary, CONVERT(VARCHAR(MAX), @myvarbinary) AS VarcharValue
--112233445566778899aabbccddeeff11223344 0x3131323233333434353536363737383839396161626263636464656566663131323233333434 112233445566778899aabbccddeeff11223344
```

Also, here are the supporting links for my statement;

All uses the same technique as described above.

The thing here to understand is first you need to convert / cast your VARCHAR string to VARBINARY and then re-engineer the process to verify your logic.

Please let me know if this doesn’t work for you. Hope I have answered you correctly.

Thanks,
Hasham

domingo, 4 de dezembro de 2011 23:55
• SQLUSA !

How you are sure that VARBINARY ''0x112233445566778899aabbccddeeff11223344'' is equivalent to VARCHAR ''112233445566778899aabbccddeeff11223344''.

First we need to convert our input string to VARBINaRY and then reconvert it back to VARCHAr to test our logic.

Also, see the links i have posted in my earlier post. It will help you understand where you are missing it.

Thanks, Hasham

segunda-feira, 5 de dezembro de 2011 01:15
• Sorry, I can't follow your logic. All I can say that my solution works beyond 16 bytes:

```DECLARE @hex varchar(max)=
master.dbo.fn_varbintohexstr(convert(varbinary(max), 0x112233445566778899aabbccddeeff11223344556677))
SELECT RIGHT(@hex,len(@hex)-2)
-- 112233445566778899aabbccddeeff11223344556677
```

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
• Sugerido como Resposta segunda-feira, 5 de dezembro de 2011 01:38
segunda-feira, 5 de dezembro de 2011 01:23
• The CONVERT solution is not robust:

I didn't read this thread from the beginning, so I don't know what it is all about. But there is a flaw in your code, since you don't specify the length for varchar value. Try:

```declare @k binary(20)
set @k = 0x112233445566778899aabbccddeeff11223344
select convert(varchar(40), @k, 2)```

It seems that you are suggesting a undocumented and unsupported function. That appears to be an inferior solution to me.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
segunda-feira, 5 de dezembro de 2011 08:59
• But the following does work....

```declare @k binary(20)
set @k = 0x112233445566778899aabbccddeeff11223344
select convert(varchar(38), @k, 2)
```

I never meant for my answer to be the ultimate answer to the question, merely a way of proposing a possible alternative approach.  The OP needs to decide what suits their situation.

• Sugerido como Resposta sábado, 28 de março de 2020 07:08
segunda-feira, 5 de dezembro de 2011 09:23
• The solution posted by Kev Riley is the most appropriate, but it needs a slight tweak.

As Kalman said this gives the incorrect output:

```declare @k binary(20)
set @k = 0x112233445566778899aabbccddeeff11223344
select convert(varchar, @k, 2)
-- 112233445566778899AABBCCDDEEFF

```

...

```SELECT CONVERT(VARCHAR,0x112233445566778899aabbccddeeff11223344,2)	   --112233445566778899AABBCCDDEEFF
SELECT CONVERT(VARCHAR(30),0x112233445566778899aabbccddeeff11223344,2) --112233445566778899AABBCCDDEEFF

SELECT CONVERT(VARCHAR(38),0x112233445566778899aabbccddeeff11223344,2)  --112233445566778899AABBCCDDEEFF11223344
SELECT CONVERT(VARCHAR(MAX),0x112233445566778899aabbccddeeff11223344,2) --112233445566778899AABBCCDDEEFF11223344
```

Jon
• Editado segunda-feira, 5 de dezembro de 2011 09:39
• Sugerido como Resposta terça-feira, 6 de dezembro de 2011 03:46
segunda-feira, 5 de dezembro de 2011 09:39
• Kalman,

exactly!  If the OP only wants to ever convert 16byte values (or 20byte values or 38byte values, or whatever) , then this is a possible, documented, supported solution.

I am trying to give alternatives.

• Sugerido como Resposta segunda-feira, 5 de dezembro de 2011 10:02
segunda-feira, 5 de dezembro de 2011 09:45
• That is fine. Let the OP decide which one meets the requirements. I agree with everybody that documented solution is preferred. Thanks all.

The following 2 solutions appear to work based on limited testing:

```-- Using the CONVERT function
SELECT CONVERT(VARCHAR(MAX),0x112233445566778899aabbccddeeff112233445566778899aabbccddeeff112233445566778899aabbccdd,2)
-- 112233445566778899AABBCCDDEEFF112233445566778899AABBCCDDEEFF112233445566778899AABBCCDD

-- Undocumented function
DECLARE @hex varchar(max)=
master.dbo.fn_varbintohexstr(convert(varbinary(max), 0x112233445566778899aabbccddeeff112233445566778899aabbccddeeff112233445566778899aabbccdd))
SELECT RIGHT(@hex,len(@hex)-2)
-- 112233445566778899aabbccddeeff112233445566778899aabbccddeeff112233445566778899aabbccdd```

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

• Editado segunda-feira, 5 de dezembro de 2011 14:08
segunda-feira, 5 de dezembro de 2011 09:57
• The solution posted above does not work.  This is the result:

112233445566778899AABBCCDDEEFF1122334400

Yes, that was the input:

```declare @k binary(20)
set @k = 0x112233445566778899aabbccddeeff11223344
select convert(varchar(40), @k, 2), @k```

The two hex strings certainly looks similar to me. Yes, you did not type the last two 00, but since you declared @k as binary(20) (as opposed to varbinar(20)), they are there.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
segunda-feira, 5 de dezembro de 2011 12:03
• declare @k binary(20)
set @k = 0x112233445566778899aabbccddeeff11223344
select @k, convert(VARCHAR(256), @k, 2)
-- 0x112233445566778899AABBCCDDEEFF1122334400  112233445566778899AABBCCDDEEFF1122334400

It is robust... well almost. But remember that "VARCHAR" is equivalent to "VARCHAR(25)" so if You will convert number longer than 25 digits , You need to use longer string to convert to.

About that "almost" part - convert might add additional zeroes at end... so using this method might not be best for Your solution. That is, if You need to get value 0x1 (in BINARY(2)) thinking it's 0x0001 and not 0x0100. Well if You will keep length of binary in check with value You use, it will be fine.

• Editado sexta-feira, 3 de junho de 2016 13:03
sexta-feira, 3 de junho de 2016 12:57
• I don't know why Kev Riley's first answer was unproposed as an answer... it doesn't take much imagination to know that if you have something bigger, you just need to define the width of the VARCHAR to be able to handle it.  One should also take the time to read the documentation on CONVERT.

Heh... remember... "There is no spoon" and that includes "silver spoons". :D

And, yeah... I know I'm necroing a nearly decade old post.

--Jeff Moden

sábado, 28 de março de 2020 07:13
• declare @k binary(20)
set @k = 0x112233445566778899aabbccddeeff11223344
select @k, convert(VARCHAR(256), @k, 2)
-- 0x112233445566778899AABBCCDDEEFF1122334400  112233445566778899AABBCCDDEEFF1122334400

It is robust... well almost. But remember that "VARCHAR" is equivalent to "VARCHAR(25)" so if You will convert number longer than 25 digits , You need to use longer string to convert to.

About that "almost" part - convert might add additional zeroes at end... so using this method might not be best for Your solution. That is, if You need to get value 0x1 (in BINARY(2)) thinking it's 0x0001 and not 0x0100. Well if You will keep length of binary in check with value You use, it will be fine.

It's not the CONVERT that will add the extra zeros.  It's the fact that someone used a non variable width BINARY() to insert the original data into.  Run the following code to prove it.

```DECLARE  @BinFixed BINARY(20)    = 0x01
,@BinVar   VARBINARY(20) = 0x01
;
SELECT  BinFixedConv = CONVERT(VARCHAR(50),@BinFixed,2)
,BinVarConv  = CONVERT(VARCHAR(50),@BinVar,2)
;
SELECT  BinFixedNoConv = @BinFixed
,BinVarNoConv   = @BinVar
;```

--Jeff Moden