Finding filetypes stored in a MySQL table

If your system allows fileuploads, and you have a database table where you store all the fileuploads, it could be interesting to see what types and how many types of files are being uploaded to your system. This query will find all filetypes uploaded to the system.

Replace the following values:

• field_for_filename – The field name containing the filename.
• table_name – The table containing the data

SELECT substring_index(lower(field_for_filename), '.', -1) as "Filetype",
count(substring_index(lower(field_for_filename), '.', -1)) as "Number of files"
FROM table_name
GROUP BY substring_index(lower(field_for_filename), '.', -1) ;

Replacing all of the selected values with your chosen value

If you for some reason need to replace some text, even inline text, with another this query can help you with that. For example if you need to update an IP address in a table, this query would solve that for you.

All the query basically does is that it looks for old_value text inside field_names values. If it finds old_value in a row it will update update that row with the old_value replaced with new_value. All other text within the field_name will stay the same except for the old_value turning into the new_value.

Replace the following value:

• field_name– The field name containing the data you want to replace.
• table_name – The table containing the data
• old_value – The value you want replaced
• new_value – The value you want ti replace old_value with

UPDATE table_name SET field_name = replace(field_name, 'old_value', 'new_value') WHERE field_name = '%old_value%'

You could most likely drop the WHERE clause and this update would still function as intended.