Hexadecimal Represented As Varchar

[Mavyak]

I need to convert an integer value to hexadecimal and then concatenate the returned value with a variable of type varchar. For instance, I have a field called uniqueid of type integer and another called mytext of type varchar(1).

If I select both fields like so:

SELECT *
FROM MyTable

I get the following result:

16884167 A

If I change that to:

SELECT CONVERT(VARBINARY(8), uniqueid), MyText
FROM MyTable

I get the following result:

0x0101A1C7 A

I now need to concatenate those two values with a period between so my result would be:

0x0101A1C7.A

But I cannot find a way to convert the represented hexadecimal value to a concatenatable datatype. All avenues I've tried get me a single character representation of the underlying integer value from an ASCII chart.

Any help would be greatly appreciated.

Mavyak

[CreganTur]

Hmmm...

Just a shot in the dark, but could you use the Str function to convert your hexidecimal result into a String, which is concatenatable (great word).

[stanl]

was one of the things your tried

CAST( CONVERT(VARBINARY(8), uniqueid) AS VARCHAR(10) )

Stan

[Mavyak]

Hmmm...

Just a shot in the dark, but could you use the Str function to convert your hexidecimal result into a String, which is concatenatable (great word).

"Msg 206, Level 16, State 2, Line 1
Operand type clash: varbinary is incompatible with float"

was one of the things your tried

CAST( CONVERT(VARBINARY(8), uniqueid) AS VARCHAR(10) )

Stan

Yes. That returns "

??" when uniqueid = 16884167




I appreciate the suggestions. Keep 'em comin'!

[stanl]

hmmm.. then I'd be curious about the error code for

SELECT CONCAT(CONVERT(VARBINARY(8), uniqueid),'.', MyText ) AS alltogether

[Mavyak]

The error code I get for that line is:

SELECT CONCAT(CONVERT(VARBINARY(8), 16884167),'.', MyText ) AS alltogether

Msg 195, Level 15, State 10, Line 1
'CONCAT' is not a recognized built-in function name.

I'm looking for something along the line of:

SELECT (CONVERT(VARBINARY(8), 16884167) + '.' + 'A') AS alltogether

[stanl]

My bad... I thought you were using SQL Server... can the SQL you use call UDF's, so if we create a Dec2Hex function you can reference it?

[Mavyak]

I am using SQL Server. I probably have a limited version of it, though. I thought about writing a CLR assembly in C# and attaching it but it just seemed like too much effort for such a small thing. I might have to do just that, though.

[stanl]

I am using SQL Server. I probably have a limited version of it, though. I thought about writing a CLR assembly in C# and attaching it but it just seemed like too much effort for such a small thing. I might have to do just that, though.

hmmm... even the freeware SQL Server Express supports CONCAT, and you already wrote CAST was supported. Perhaps you could link the table to an .mdb file and query through that using VBA conversion functions.

[Mavyak]

I can use CONCAT from Visual Studio but I work pretty exclusively in the SQL Server Management Studio. I may try the Access route or I may get better acquainted with Visual Studio instead. I'd prefer an SQL solution, but if I have to write code (other than SQL) then I'll do it.

[stanl]

I can use CONCAT from Visual Studio but I work pretty exclusively in the SQL Server Management Studio. I may try the Access route or I may get better acquainted with Visual Studio instead. I'd prefer an SQL solution, but if I have to write code (other than SQL) then I'll do it.

I'm a little more confused. CONCAT is part of T-SQL repetoire and so I would assume it wouldn't matter what you used as long as you had a correct connection string - you could run the SQL from an Excel macro, if needed.... maybe you can CAST the date directly to VARCHAR(10), then use the + concatenation operator... If I get time today, I'll try to replicate your problem on our SQL Server.

[Mavyak]

I think it's a matter of syntax.

This gives me the error:

SELECT CONCAT('A', 'B')

But this does not:

SELECT  { fn CONCAT('a', 'b') } AS Expr1

[stanl]

Yeah,

My experience with CONCAT is working with XML datatypes and Unicode, and as I remember the syntax is skewed. Point is will it work for your SELECT or must we dig further?

As they just fired our network manager and Inventory manager, I'm having to move away from programming and into PIX Firewalls, ADSI, cabling, Terminal Services and a host of other areas where the dear departed left no documentation... but to be honest, problems like this keep me sane. Stan

[Mavyak]

It would be nice to have a solution as opposed to a workaround, but for now, my coworker wrote a VBA function to do it with a linked table in Access.

[stanl]

It would be nice to have a solution as opposed to a workaround, but for now, my coworker wrote a VBA function to do it with a linked table in Access.

Good old Plan-B .....