Function to convertbinary to varchar

Question

Function to convertbinary to varchar

pzmrcd

Default port

Points: 1419
More actions
May 28, 2008 at 1:15 pm

#380742

Hi
I have written a udf to convert from binary to varchar..I get the following error when I run it...any help.Thanks
When created error:Cannot add rows to sysdepends for the current object because it depends on the missing object 'master..xp_varbintohexstr'. The object will still be created.
When I try to run it: Could not find stored procedure 'master..xp_varbintohexstr'.
ALTER FUNCTION [dbo].[GetImageURL]
(@ID varbinary(50))
RETURNS varchar(50)
AS
BEGIN
DECLARE @string varchar(50)
DECLARE @binary binary(6)
SELECT @binary = @ID
EXEC master..xp_varbintohexstr @binary, @string OUTPUT
Return 'P'+ @string + '.JPG'
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

The procedure master..xp_varbintohexstr is not a system extended stored procedure in SQL 2005. Is this something you added?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

You're going to want to tap into CLR for this. That custom extended procedure doesn't exist in a SQL Server 2005 install.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

No UDF required... certainly, no CLR required... 😉

DECLARE @Varbinary VARBINARY(8000)

SET @Varbinary = 0x5468697320697320612074657374

SELECT @Varbinary

SELECT CAST(@VarBinary AS VARCHAR(8000))

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Jeff Moden (5/29/2008)
No UDF required... certainly, no CLR required... 😉
DECLARE @Varbinary VARBINARY(8000)
SET @Varbinary = 0x5468697320697320612074657374
SELECT @Varbinary
SELECT CAST(@VarBinary AS VARCHAR(8000))

Nice trick Jeff...but that's not a hex string being returned...... He's looking for a "binToHex" conversion routine.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

OP is passing in Varbinary... I guess I don't understand... what does the input actually look like and what should the output be?

Are you suggesting that the input is in hex without the "0x" associated with varbinary?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)