none
WHERE clause for column defined varbinary(4000) - again

    Question

  • OK, I asked this question earlier, but got it all wrong. So I am asking again in a new thread.

    I have a table that has a varbinary(4000) column. When I do a SELECT VARBINARY_COLUMN FROM TABLE1 I get this data returned.

    0xF1F4F1C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3F0F3F0F0F2F0F1F260F0F960F3F000010C00001C00000CF9F9F9F9F9D5C4F9F9F9F9F9C10000000148352CC24000000000000C00000C40E4C4C5D7D6E2C9E340C1E2E2C5E2E2D4C5D5E340404040404040404040404040404040400000000C0000000C00000000152C00000000000C00000000000C4EC3D6D3000C0000000000800CD540000001900C00000000000C000000000C000000000C00000000000C000000000C000001900C00000000000C000000000C0000000000152C999C000CF9F9F9F9F9F9C4C5D7404040404040404040404040404040404040404040404040404040404040404040404040C1000000000C000000000C

    0xF1F4F2C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3F0F3F0F0F2F0F1F260F0F960F3F000010C00001C00000CF9F9F9F9F9D5C4F9F9F9F9F9C10000000148352CC24000000000000C00000C40E4C4C5D7D6E2C9E340C1E2E2C5E2E2D4C5D5E340404040404040404040404040404040400000000C0000000C00000000152C00000000000C00000000000C4EC3D6D3000C0000000000800CD540000001900C00000000000C000000000C000000000C00000000000C000000000C000001900C00000000000C000000000C0000000000152C999C000CF9F9F9F9F9F9C4C5D7404040404040404040404040404040404040404040404040404040404040404040404040C1000000000C000000000C

    0xF1F4F2C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3F0F3F0F0F2F0F1F260F0F960F3F000010C00001C00000CF9F9F9F9F9D5C4F9F9F9F9F9C10000000148352CC24000000000000C00000C40E4C4C5D7D6E2C9E340C1E2E2C5E2E2D4C5D5E340404040404040404040404040404040400000000C0000000C00000000152C00000000000C00000000000C4EC3D6D3000C0000000000800CD540000001900C00000000000C000000000C000000000C00000000000C000000000C000001900C00000000000C000000000C0000000000152C999C000CF9F9F9F9F9F9C4C5D7404040404040404040404040404040404040404040404040404040404040404040404040C1000000000C000000000C

    0xF1F4F3C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3F0F3F0F0F2F0F1F260F0F960F3F000010C00001C00000CF9F9F9F9F9D5C4F9F9F9F9F9C10000000148352CC24000000000000C00000C40E4C4C5D7D6E2C9E340C1E2E2C5E2E2D4C5D5E340404040404040404040404040404040400000000C0000000C00000000152C00000000000C00000000000C4EC3D6D3000C0000000000800CD540000001900C00000000000C000000000C000000000C00000000000C000000000C000001900C00000000000C000000000C0000000000152C999C000CF9F9F9F9F9F9C4C5D7404040404040404040404040404040404040404040404040404040404040404040404040C1000000000C000000000C

    How would I code a SELECT statement to return the rows that start with '0xF1F4F2C4'.

    Tuesday, November 05, 2013 10:41 PM

Answers

  • Try the following:

    CREATE TABLE #T (ID INT IDENTITY(1,1), VARB VARBINARY(4000));
    GO
    INSERT #T (VARB) VALUES (0xF2F4F1C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3F0F3F0F0F2F0F1F260F0F960F3F000010C00001C00000CF9F9F9F9F9D5C4F9F9F9F9F9C10000000148352CC24000000000000C00000C40E4C4C5D7D6E2C9E340C1E2E2C5E2E2D4C5D5E340404040404040404040404040404040400000000C0000000C00000000152C00000000000C00000000000C4EC3D6D3000C0000000000800CD540000001900C00000000000C000000000C000000000C00000000000C000000000C000001900C00000000000C000000000C0000000000152C999C000CF9F9F9F9F9F9C4C5D7404040404040404040404040404040404040404040404040404040404040404040404040C1000000000C000000000C);
    GO 100
    INSERT #T (VARB) VALUES (0xF1F4F2C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3F0F3F0F0F2F0F1F260F0F960F3F000010C00001C00000CF9F9F9F9F9D5C4F9F9F9F9F9C10000000148352CC24000000000000C00000C40E4C4C5D7D6E2C9E340C1E2E2C5E2E2D4C5D5E340404040404040404040404040404040400000000C0000000C00000000152C00000000000C00000000000C4EC3D6D3000C0000000000800CD540000001900C00000000000C000000000C000000000C00000000000C000000000C000001900C00000000000C000000000C0000000000152C999C000CF9F9F9F9F9F9C4C5D7404040404040404040404040404040404040404040404040404040404040404040404040C1000000000C000000000C);
    GO
    SELECT * FROM #T WHERE LEFT(VARB,4) = 0xF1F4F2C4;
    GO
    DROP TABLE #T;
    GO
    -- 101	0xF1F4F2C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3F0F3F0F0F2F0F1F260F0F960F3F000010C00001C00000CF9F9F9F9F9D5C4F9F9F9F9F9C10000000148352CC24000000000000C00000C40E4C4C5D7D6E2C9E340C1E2E2C5E2E2D4C5D5E340404040404040404040404040404040400000000C0000000C00000000152C00000000000C00000000000C4EC3D6D3000C0000000000800CD540000001900C00000000000C000000000C000000000C00000000000C000000000C000001900C00000000000C000000000C0000000000152C999C000CF9F9F9F9F9F9C4C5D7404040404040404040404040404040404040404040404040404040404040404040404040C1000000000C000000000C


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, November 05, 2013 11:17 PM
  • Cast the 0xF1F4F2C4 as a string and use LIKE

    Declare @Test Table(MyData varbinary(4000));
    Insert @Test(MyData) Values
    (0xF1F4F1C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3 /* and so on */),
    (0xF1F4F2C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3),
    (0xF1F4F2C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3),
    (0xF1F4F3C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3);
    Select * From @Test
    Where MyData Like Cast(0xF1F4F2C4 As varchar(4000)) + '%';
    Tom

    Tuesday, November 05, 2013 11:17 PM

All replies

  • Cast the 0xF1F4F2C4 as a string and use LIKE

    Declare @Test Table(MyData varbinary(4000));
    Insert @Test(MyData) Values
    (0xF1F4F1C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3 /* and so on */),
    (0xF1F4F2C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3),
    (0xF1F4F2C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3),
    (0xF1F4F3C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3);
    Select * From @Test
    Where MyData Like Cast(0xF1F4F2C4 As varchar(4000)) + '%';
    Tom

    Tuesday, November 05, 2013 11:17 PM
  • Try the following:

    CREATE TABLE #T (ID INT IDENTITY(1,1), VARB VARBINARY(4000));
    GO
    INSERT #T (VARB) VALUES (0xF2F4F1C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3F0F3F0F0F2F0F1F260F0F960F3F000010C00001C00000CF9F9F9F9F9D5C4F9F9F9F9F9C10000000148352CC24000000000000C00000C40E4C4C5D7D6E2C9E340C1E2E2C5E2E2D4C5D5E340404040404040404040404040404040400000000C0000000C00000000152C00000000000C00000000000C4EC3D6D3000C0000000000800CD540000001900C00000000000C000000000C000000000C00000000000C000000000C000001900C00000000000C000000000C0000000000152C999C000CF9F9F9F9F9F9C4C5D7404040404040404040404040404040404040404040404040404040404040404040404040C1000000000C000000000C);
    GO 100
    INSERT #T (VARB) VALUES (0xF1F4F2C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3F0F3F0F0F2F0F1F260F0F960F3F000010C00001C00000CF9F9F9F9F9D5C4F9F9F9F9F9C10000000148352CC24000000000000C00000C40E4C4C5D7D6E2C9E340C1E2E2C5E2E2D4C5D5E340404040404040404040404040404040400000000C0000000C00000000152C00000000000C00000000000C4EC3D6D3000C0000000000800CD540000001900C00000000000C000000000C000000000C00000000000C000000000C000001900C00000000000C000000000C0000000000152C999C000CF9F9F9F9F9F9C4C5D7404040404040404040404040404040404040404040404040404040404040404040404040C1000000000C000000000C);
    GO
    SELECT * FROM #T WHERE LEFT(VARB,4) = 0xF1F4F2C4;
    GO
    DROP TABLE #T;
    GO
    -- 101	0xF1F4F2C4F0F0F0F0F0F0F0F0F0F0F0F0F0F1F2F3F0F3F0F0F2F0F1F260F0F960F3F000010C00001C00000CF9F9F9F9F9D5C4F9F9F9F9F9C10000000148352CC24000000000000C00000C40E4C4C5D7D6E2C9E340C1E2E2C5E2E2D4C5D5E340404040404040404040404040404040400000000C0000000C00000000152C00000000000C00000000000C4EC3D6D3000C0000000000800CD540000001900C00000000000C000000000C000000000C00000000000C000000000C000001900C00000000000C000000000C0000000000152C999C000CF9F9F9F9F9F9C4C5D7404040404040404040404040404040404040404040404040404040404040404040404040C1000000000C000000000C


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, November 05, 2013 11:17 PM