0

I want to get file type from binary data in a column File_Stream in FileTable.

In FileTable, there is File_Type column but it's not reliable.

Pay attention to the following queries:

These files are the same!

PIC

Thom A
  • 88,727
  • 11
  • 45
  • 75
AAA
  • 21
  • 6
  • Post your information as test in your post not links to images. And if File_Type is not reliable then you have a problem in the way you are storing files. When a file is uploaded it should be saved with the correct file type or extension. In short, you can't determine a file's type from a plain byte array (https://stackoverflow.com/questions/1654846/in-c-how-can-i-know-the-file-type-from-a-byte) – Ryan Wilson Jul 02 '19 at 11:34
  • 3
    There is no 100% foolproof way of doing this. Types like HTML and plain text don't have markers that allow you to detect the file type, for example. You're looking for the moral equivalent of the Unix [`file(1)`](https://linux.die.net/man/1/file) utility, which is somewhat possible but has no native implementation in SQL Server (and setting up a CLR assembly with this functionality, while possible, is a lot of work). – Jeroen Mostert Jul 02 '19 at 11:34
  • If the extension of the file in it's name is wrong, so too will the File Type column. The file type is determined by the **last** extension on the file's name. So a file called `MyFile.txt.csv.xlsx.bin` would be seen by your File Stream as a `bin` file. This is true for your operating system as well. That same file would be recognise as a `bin` file by both Windows and Linux – Thom A Jul 02 '19 at 11:47

1 Answers1

3

The file_type column value is derived from the file name, not the binary contents of the file. SQL Server has no notion of what the actual binary contents represent.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71