2

Here is a primary example: I have a varbinary field in sql server that stores and image. I need to determine the middle of the image grab the 4 digit byte and use that to determine the color. I have no idea how I determine what to look at in that field and then what part of the image field I look at to determine the proper color.

Here is what we have:

select
WHEN dbo.prod_Color.ColorGraphic IS NULL 
THEN 'FFFFFF' 
WHEN SUBSTRING(dbo.prod_Color.ColorGraphic,5, 1) = 0x02 
        THEN 
            CONVERT(varchar(2), SUBSTRING(dbo.prod_Color.ColorGraphic, 135, 1), 2) 
            + CONVERT(varchar(2), SUBSTRING(dbo.prod_Color.ColorGraphic, 134, 1), 2) 
            + CONVERT(varchar(2), SUBSTRING(dbo.prod_Color.ColorGraphic, 133, 1), 2) 
    WHEN SUBSTRING(dbo.prod_Color.ColorGraphic, 5, 1) = 0x03 
        THEN 
            CONVERT(varchar(2), SUBSTRING(dbo.prod_Color.ColorGraphic, 157, 1), 2) 
            + CONVERT(varchar(2), SUBSTRING(dbo.prod_Color.ColorGraphic, 156, 1), 2) 
            + CONVERT(varchar(2), SUBSTRING(dbo.prod_Color.ColorGraphic, 155, 1), 2) 
        ELSE 
            'FFFFFF' 
        END AS HtmlColor

it was previously determined that we could use the substring 5, 1 and that indicated 2 types of bmp format... then do a case statement on it. I don't believe this to be correct but regardless we've been tasked with finding the middle byte and using that value to do the compare.

Has anyone done this or can you give me a kick start in what to do.

Dan Colgan
  • 21
  • 3
  • Hi and welcome to SO. I don't think doing this in sql is the right tool for the job. This would be much easier in a programming language. – Sean Lange Aug 06 '19 at 20:56
  • 1
    Thanks for your reply Sean but I don't believe whether or not it is done in SQL is really the issue. How would it determine based on a '0x02' what color that pixel is. – Dan Colgan Aug 06 '19 at 21:08
  • This question is similar: https://stackoverflow.com/questions/26225051/detecting-color-of-an-image-stored-in-the-database – LaraRaraBoBara Aug 06 '19 at 22:08
  • 1
    You could use a darning needle to drive a nail into a piece of wood, too, but that doesn't make it the right tool for the job. If you have access to SQL CLR I would recommend writing this in an SQL CLR function that loads the `ColorGraphic` varbinary into a [`System.Drawing.Bitmap`](https://learn.microsoft.com/en-us/dotnet/api/system.drawing.bitmap) object and appropriately uses [`Bitmap.GetPixel()`](https://learn.microsoft.com/en-us/dotnet/api/system.drawing.bitmap.getpixel) to look up the color. – AlwaysLearning Aug 07 '19 at 06:36
  • I think you missed my point. You tagged this with sql-server and asked about doing this in a query. I stand by my suggestion that doing this with a query is the wrong tool. You then said "How would it determine...". The problem there is the **it**. That needs to be a programming language, not t-sql. As suggested using CLR would be an option. In that case the **it** becomes .NET. But most languages can tell you the color of a pixel. How do you determine the middle pixel? Load it into a byte array and finding the middle is trivial. – Sean Lange Aug 07 '19 at 15:37

0 Answers0