Converting between binary hex and GUID (uniqueidentifier)

  • jay-h

    SSCoach

    Points: 18801

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    ...

    -- FORTRAN manual for Xerox Computers --

  • Lynn Pettis

    SSC Guru

    Points: 442091

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    Well, there is this:

    declare @TestUI uniqueidentifier = '43367830-4437-3543-3132-304538434139';

    select @TestUI, '43367830-4437-3543-3132-304538434139';

  • Y.B.

    SSChampion

    Points: 11244

    This seems to work fine for me unless I'm missing something...

    DECLARE @GUID UNIQUEIDENTIFIER, @binary VARBINARY(16)

    SELECT @GUID = NEWID()

    SELECT @binary = CAST(@guid AS VARBINARY(16))

    SELECT
    @GUID AS Here,
    @binary AS There,
    CAST(@binary AS UNIQUEIDENTIFIER) AS BackAgain


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • Lynn Pettis

    SSC Guru

    Points: 442091

    Lynn Pettis - Thursday, November 8, 2018 1:02 PM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    Well, there is this:

    declare @TestUI uniqueidentifier = '43367830-4437-3543-3132-304538434139';

    select @TestUI, '43367830-4437-3543-3132-304538434139';

    And after playing a little more:

    declare @TestUI uniqueidentifier = '43367830-4437-3543-3132-304538434139'
       ,@TestBin binary(16) = 0x6C7DC5120E8CA94F8224D0198C9D7CF2;

    select @TestUI, '43367830-4437-3543-3132-304538434139';

    select cast(@TestUI as varbinary(16)), cast(cast(@TestUI as varbinary(16)) as uniqueidentifier);

    select
    @TestBin
    , convert(varchar(36),@TestBin,2)
    , stuff(stuff(stuff(stuff(convert(varchar(36),@TestBin,2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-')
    , cast(stuff(stuff(stuff(stuff(convert(varchar(36),@TestBin,2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier)

  • Jeff Moden

    SSC Guru

    Points: 993644

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    This will do it for you.  I also took the liberty of splitting the desired value out of the original message.


    --===== This isn't part of the solution.  We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
         -- converts the text to a BINARY(16) and then converts that to the final GUID.
     SELECT CONVERT(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    ;

    For more information on the convert format of "1" that I used, search for "Binary Style" in the article at the following link...
    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Lynn Pettis

    SSC Guru

    Points: 442091

    Jeff Moden - Thursday, November 8, 2018 10:05 PM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    This will do it for you.  I also took the liberty of splitting the desired value out of the original message.


    --===== This isn't part of the solution.  We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
         -- converts the text to a BINARY(16) and then converts that to the final GUID.
     SELECT CONVERT(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    ;

    For more information on the convert format of "1" that I used, search for "Binary Style" in the article at the following link...
    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

    Actually, Jeff, your code doesn't quite work.  Look at the following:

    --===== This isn't part of the solution. We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
      -- converts the text to a BINARY(16) and then converts that to the final GUID.
    SELECT SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), convert(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    select SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), cast(stuff(stuff(stuff(stuff(convert(varchar(36),convert(varbinary(16),substring(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1),2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier);
    ;

  • jay-h

    SSCoach

    Points: 18801

    Thanks for the input. Monday I hope to dig back into this.

    ...

    -- FORTRAN manual for Xerox Computers --

  • Jeff Moden

    SSC Guru

    Points: 993644

    Lynn Pettis - Friday, November 9, 2018 8:44 AM

    Jeff Moden - Thursday, November 8, 2018 10:05 PM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    This will do it for you.  I also took the liberty of splitting the desired value out of the original message.


    --===== This isn't part of the solution.  We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
         -- converts the text to a BINARY(16) and then converts that to the final GUID.
     SELECT CONVERT(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    ;

    For more information on the convert format of "1" that I used, search for "Binary Style" in the article at the following link...
    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

    Actually, Jeff, your code doesn't quite work.  Look at the following:

    --===== This isn't part of the solution. We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
      -- converts the text to a BINARY(16) and then converts that to the final GUID.
    SELECT SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), convert(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    select SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), cast(stuff(stuff(stuff(stuff(convert(varchar(36),convert(varbinary(16),substring(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1),2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier);
    ;

    Ah... be careful now, Lynn.  You have to remember that the value of 0x6C7DC5120E8CA94F8224D0198C9D7CF2 is NOT a GUID.  It's the binary (hex) representation for the GUID.  The code I posted is correct.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Lynn Pettis

    SSC Guru

    Points: 442091

    Jeff Moden - Friday, November 9, 2018 8:13 PM

    Lynn Pettis - Friday, November 9, 2018 8:44 AM

    Jeff Moden - Thursday, November 8, 2018 10:05 PM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    This will do it for you.  I also took the liberty of splitting the desired value out of the original message.


    --===== This isn't part of the solution.  We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
         -- converts the text to a BINARY(16) and then converts that to the final GUID.
     SELECT CONVERT(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    ;

    For more information on the convert format of "1" that I used, search for "Binary Style" in the article at the following link...
    https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

    Actually, Jeff, your code doesn't quite work.  Look at the following:

    --===== This isn't part of the solution. We're just creating the message as a test.
    DECLARE @Msg VARCHAR(1000) = '[Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)'')'
    ;
    --===== This splits the "binary" (hex, really) from the message and then, using CONVERT with a "1" format,
      -- converts the text to a BINARY(16) and then converts that to the final GUID.
    SELECT SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), convert(UNIQUEIDENTIFIER,CONVERT(BINARY(16),SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1))
    select SUBSTRING(@Msg,CHARINDEX('Job 0x',@Msg)+4,34), cast(stuff(stuff(stuff(stuff(convert(varchar(36),convert(varbinary(16),substring(@Msg,CHARINDEX('Job 0x',@Msg)+4,34),1),2),21,0,'-'),17,0,'-'),13,0,'-'),9,0,'-') as uniqueidentifier);
    ;

    Ah... be careful now, Lynn.  You have to remember that the value of 0x6C7DC5120E8CA94F8224D0198C9D7CF2 is NOT a GUID.  It's the binary (hex) representation for the GUID.  The code I posted is correct.

    But if you compare that value to the value from the other table it matches.  At least that is what I was seeing the OPs original post.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182321

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    The reason this is not working is that you are comparing a string and a binary that "look" the same.
    😎

    0x6C7DC5120E8CA94F8224D0198C9D7CF2 is not the same as '0x6C7DC5120E8CA94F8224D0198C9D7CF2', you are using the latter when you should be using the former.

    CONVERT(UNIQUEIDENTIFIER, 0x6C7DC5120E8CA94F8224D0198C9D7CF2,1);

    To convert a string into binary, one has to use the convert function with the format parameter value of 1.


    DECLARE @BINSTR VARCHAR(36) = '0x6C7DC5120E8CA94F8224D0198C9D7CF2';
    SELECT
      @BINSTR
     ,CONVERT(VARBINARY(16),@BINSTR,0) BAD_BIN
     ,CONVERT(VARBINARY(16),@BINSTR,1) OK_BIN
    ;

  • Jeff Moden

    SSC Guru

    Points: 993644

    Eirikur Eiriksson - Saturday, November 10, 2018 12:28 AM

    jay-h - Thursday, November 8, 2018 12:49 PM

    Sounds easy. but I'm trying to connect the [step_uid] field in msdb.dbo.sysjob (uniqueidentifier) to the text based binary version in in master.dbo.sysprocesses [Program_name] (example ''SQLAgent - TSQL JobStep (Job 0x6C7DC5120E8CA94F8224D0198C9D7CF2 : Step 1)')

    I've been searching Google, but the examples posted seems to be people glibly suggesting using convert (or cast) as below. But this is simply NOT working. For example, if I try to convert the value (from above example) as suggested by a number of people:

    print convert(uniqueidentifier, convert(binary(16), '0x6C7DC5120E8CA94F8224D0198C9D7CF2'))

    Result: 43367830-4437-3543-3132-304538434139

    The result of this approach is not correct. For one thing, it has no (hex) letters and does not match up to any jobs. In fact no conversions using this technique seem to every show letters--even though the values quoted in .sysjobs certainly do.

    If I try to reverse the process which SHOULD get me tack to where I started
    print convert(binary(16), '43367830-4437-3543-3132-304538434139')

    I get this: 0x34333336373833302D343433372D3335, which is nonsensical.

    Bottom line: does ANYONE have a reliable way to translate between binary and GUID (uniqueidentifier) formats?

    The reason this is not working is that you are comparing a string and a binary that "look" the same.
    😎

    0x6C7DC5120E8CA94F8224D0198C9D7CF2 is not the same as '0x6C7DC5120E8CA94F8224D0198C9D7CF2', you are using the latter when you should be using the former.

    CONVERT(UNIQUEIDENTIFIER, 0x6C7DC5120E8CA94F8224D0198C9D7CF2,1);

    To convert a string into binary, one has to use the convert function with the format parameter value of 1.


    DECLARE @BINSTR VARCHAR(36) = '0x6C7DC5120E8CA94F8224D0198C9D7CF2';
    SELECT
      @BINSTR
     ,CONVERT(VARBINARY(16),@BINSTR,0) BAD_BIN
     ,CONVERT(VARBINARY(16),@BINSTR,1) OK_BIN
    ;

    Exactly.  And the code I demonstrated also splits the character-based binary out of the original string.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • jay-h

    SSCoach

    Points: 18801

    Ah the '1' argument is what I was missing. 

    CONVERT(VARBINARY(16),@BINSTR,1) OK_BIN

    Thanks everyone

    ...

    -- FORTRAN manual for Xerox Computers --

  • Jeff Moden

    SSC Guru

    Points: 993644

    jay-h - Monday, November 12, 2018 12:31 PM

    Ah the '1' argument is what I was missing. 

    CONVERT(VARBINARY(16),@BINSTR,1) OK_BIN

    Thanks everyone

    Except ou know it's always going to be exactly 16 bytes so don't use VARBINARY(16).  Use BINARY(16), instead.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 13 posts - 1 through 13 (of 13 total)

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