How to concatenate binary variable with string?

• john0990

  SSC Journeyman

  Points: 98

  More actions

  July 7, 2009 at 1:29 pm

  #139647

  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. How to do this?

  Thanks in advance!

• SwePeso

  SSC-Dedicated

  Points: 39758

  More actions

  July 7, 2009 at 1:45 pm

  #1021308

  DECLARE @mybin1 binary(8)

  SET @mybin1 = 0x0000000000045F7C

  PRINT CONVERT(varchar(8), @mybin1) + ' TEST'

  SELECTmaster.sys.fn_varbintohexstr(@mybin1) + ' TEST'

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• john0990

  SSC Journeyman

  Points: 98

  More actions

  July 7, 2009 at 2:28 pm

  #1021327

  Thanks so much! That worked great!

• juanfvc

  Valued Member

  Points: 68

  More actions

  May 31, 2011 at 11:42 am

  #1332161

  Awsome.!!!! thanks..!!!!