Concatenating binary variable to string

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenating binary variable to string

Author

Topic

john0990
Starting Member

4 Posts

Posted - 2009-07-07 : 11:17:34

Hello, I am having a problem concatenating a binary variable to a string. A simplified example:

DECLARE @mybin1 binary(8)
SET @mybin1 = 0x0000000000045F7C
PRINT CONVERT(varchar(8), @mybin1) + ' TEST'

The result is:
strange characters.... + ' TEST'

In the end I'm using an EXEC ('SELECT...') statement within my code, and want to use the binary value in a WHERE clause for filtering purposes. For example:

DECLARE @mybin1 varchar(8)
SET @mybin1 = CONVERT(varchar(8),0x0000000000045F7C)
EXEC ('SELECT * FROM TestTable WHERE TimeStamp > ' + @mybin1)

With this I get the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '|'.

If I don't try to convert/concatenate the binary variable to the string it works as in this example:
DECLARE @mybin1 binary(8)
SET @mybin1 = 0x0000000000045F7C
SELECT * FROM TestTable WHERE TimeStamp > @mybin1

I think I need to convert the binary variable to a literal string '0x0000000000045F7C' for this to work. Any help would be appreciated! Thanks so much.

john0990
Starting Member

4 Posts

Posted - 2009-07-07 : 16:41:46

Got the answer at another forum:

DECLARE @mybin1 binary(8)
SET @mybin1 = 0x0000000000045F7C
PRINT CONVERT(varchar(8), @mybin1) + ' TEST'

SELECT master.sys.fn_varbintohexstr(@mybin1) + ' TEST'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-07 : 16:43:52

You're welcome.
http://www.sqlservercentral.com/Forums/Topic748826-338-1.aspx

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources