9

How can I get a specific range of bytes in the varbinary data?

For example length of varbinary data is 128 and I want to get only 15-19 bytes.

iamdave
  • 12,023
  • 3
  • 24
  • 53
mkus
  • 3,357
  • 6
  • 37
  • 45

1 Answers1

15
SELECT SUBSTRING(VarbinaryColumn, 15, 5)

15 being the first position and 5 the length.

More information:

Andriy M
  • 76,112
  • 17
  • 94
  • 154
  • @aF.: Thanks, would be nice to know what particularly is/can be wrong with this. – Andriy M Jan 03 '12 at 10:09
  • 2
    well after some testing it works but I've a doubt. What datatype does that substring returns? – aF. Jan 03 '12 at 10:11
  • @aF. usually input type http://msdn.microsoft.com/en-us/library/ms187748.aspx and SUBSTRING does works with varbinary. Also see http://stackoverflow.com/a/1289171/27535 from Remus – gbn Jan 03 '12 at 10:17
  • It returns `varbinary(5)`. Here's a test script: `WITH data (v) AS ( SELECT CAST(REPLICATE('a', 20) AS varbinary) ), data2 (v) AS ( SELECT SUBSTRING(v, LEN(15), 5) FROM data ) SELECT v, SQL_VARIANT_PROPERTY(v, 'BaseType'), SQL_VARIANT_PROPERTY(v, 'MaxLength') FROM data2` – Andriy M Jan 03 '12 at 10:18