Can I write my own mySQL functions to use in mySQL queries?

Question

Can I write a custom function that I can call when making mySQL queries eg.

My database table has fields 'filetype' and 'filename'. I want to perform a function on filename before returning the result.

$query = "SELECT filetype, MY_FUNCTION(filename) FROM table..";
$result = mysql_query($query);
return $result

SO $result now has the new values created by MY_FUNCTION. The original database data will be unchanged.

OK - so it looks like User Defined Functions are the way to go... how do I write my own user defined function? Can I use PHP to write it? Where do I save the function or do I include it in my globals file?

Thanks!

Answer 1

DELIMITER $$

CREATE FUNCTION MY_FUNCTION (FILENAME VARCHAR(255)) RETURNS VARCHAR(255)
DETERMINISTIC
NO SQL
BEGIN
       RETURN SUBSTR(filename, 1, 20);
END
$$


DELIMITER ;

To create the function, you just paste its code (sililar to provided above) in your favorite query tool, like mysql.exe, MySQL Query Browser, phpmysqladmin etc.

You can write UDF's both in C and in SQL. In the latter case, you don't need to have a compiler.

The function I provided as an example is an SQL UDF. It just returns first 20 characters of the filename.

You can use any MySQL functions in an SQL UDF.

Answer 2

Yes, this is called User Defined Functions (UDF).

And here is a good repository of already pre-build functions, so you can check if something fits your needs.