How to concatenate hex value with string value after removing 'ox' from hex string

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hello All ,

I have one binary input parameter (@Binval BINARY(32) )in SP
which accept value like

0x0101010101010100000000000000000000000000000000000000000000000000

Now I want to remove 0x from above value so that final value will be

0101010101010100000000000000000000000000000000000000000000000000

and want to concatenate this with some string like

SELECT 'Your binary data is : ' + @Binval

where @Binval is input parameter for SP which has Hex value

How to do this in sql

Posted 27-Feb-12 21:19pm

RDBurmon

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

N Haque · Accepted Answer · 2012-02-28T01:09:00

After converting binary, try this-

SQL

select  'Your binary data is : ' + SUBSTRING( convert(nvarchar(32), @Binval,1),3,LEN(convert(nvarchar(32), @Binval,1)))

Or

SQL

Declare @binString nVarchar(32)
Set @binString=convert(nvarchar(32), @Binval,1) -- 
select  'Your binary data is : ' + SUBSTRING( @binString,3,LEN(@binString))

Converting binary value:

SQL

@binString=convert(nvarchar(32), @Binval,0)
print @binString
@binString=convert(nvarchar(32), @Binval,1)
print @binString
@binString=convert(nvarchar(32), @Binval,2)
print @binString

Out Put of converted string :
Your binary data is : ā
Your binary data is : 010101010101010000000000000000
Your binary data is : 010101010101000000000000000000

N Haque · Accepted Answer · 2012-02-27T23:26:00

Solution 1

You try this -

SQL

select  'Your binary data is : ' + SUBSTRING(@Binval,3,LEN(@Binval))

Posted 27-Feb-12 23:26pm

N Haque

Comments

RDBurmon 28-Feb-12 5:45am

I think you have just posted with out executing it. Because above way I already known and that is never useful

finally expected result :

An error occurred
The data types varchar and varbinary are incompatible in the add operator.

N Haque 28-Feb-12 7:00am

Sorry You have to convert the binary to String
Try it -

select 'Your binary data is : ' + SUBSTRING( convert(nvarchar(32), @Binval,1),3,LEN(convert(nvarchar(32), @Binval,1)))

OR

Declare @binString nVarchar(32)
Set @binString=convert(nvarchar(32), @Binval,1)
select 'Your binary data is : ' + SUBSTRING( @binString,3,LEN(@binString))