这个是我在移植数据库后,从SQL Server移植过来的。对于SQL大侠们,这个算是毛毛雨了。
SQL Server2000的自定义函数如下:
- CREATE FUNCTION getfiletype (@kuozhanming varchar(100))
- RETURNS varchar(100)
- AS
- BEGIN
- declare @filetype varchar(100)
- declare @index int
- set @filetype=''
- if (@kuozhanming is null and @kuozhanming = '')
- select @filetype = ''
- else
- begin
- SET @kuozhanming = Reverse(@kuozhanming)
- SELECT @index = charindex('.',@kuozhanming)
- if (@index < 2)
- select @filetype = ''
- else
- begin
- SET @kuozhanming = left(@kuozhanming,@index - 1)
- SET @kuozhanming = Reverse(@kuozhanming)
- SET @kuozhanming = Lower(@kuozhanming)
- SELECT @filetype =
- CASE( @kuozhanming)
- WHEN 'doc' THEN 'application/msword'
- WHEN 'log' THEN 'application/octet-stream'
- WHEN 'ini' THEN 'application/octet-stream'
- WHEN 'exe' THEN 'application/octet-stream'
- WHEN 'dll' THEN 'application/octet-stream'
- WHEN 'rar' THEN 'application/octet-stream'
- WHEN 'txt' THEN 'text/html'
- WHEN 'html' THEN 'text/html'
- WHEN 'htt' THEN 'text/html'
- WHEN 'bmp' THEN 'image/bmp'
- WHEN 'jpg' THEN 'image/pjpeg'
- WHEN 'avi' THEN 'video/avi'
- else @kuozhanming
- end
- end
- end
- return @filetype
- END
MySQL实现相同功能的的自定义函数如下
- drop function if exists getfiletype;
- DELIMITER |
- /*
- 取出文件名中扩展名的文件名称
- 比如
- c:/1234/5678.txt
- 则返回 txt对应的 text/html;
- */
- CREATE FUNCTION getfiletype (t_kuozhanming varchar(100))
- RETURNS varchar(100)
- BEGIN
- declare t_filetype varchar(100);
- declare t_index int;
- set t_filetype='';
- if (t_kuozhanming is null or t_kuozhanming = '') then
- set t_filetype = '';
- else
- SET t_kuozhanming = Reverse(t_kuozhanming);
- SET t_index = locate('.',t_kuozhanming) ;
- if (t_index < 2) then
- set t_filetype = '';
- else
- SET t_kuozhanming = left(t_kuozhanming,t_index - 1);
- SET t_kuozhanming = Reverse(t_kuozhanming);
- SET t_kuozhanming = Lower(t_kuozhanming);
- SELECT CASE( t_kuozhanming)
- WHEN 'doc' THEN 'application/msword'
- WHEN 'log' THEN 'application/octet-stream'
- WHEN 'ini' THEN 'application/octet-stream'
- WHEN 'exe' THEN 'application/octet-stream'
- WHEN 'dll' THEN 'application/octet-stream'
- WHEN 'rar' THEN 'application/octet-stream'
- WHEN 'txt' THEN 'text/html'
- WHEN 'html' THEN 'text/html'
- WHEN 'htt' THEN 'text/html'
- WHEN 'bmp' THEN 'image/bmp'
- WHEN 'jpg' THEN 'image/pjpeg'
- WHEN 'avi' THEN 'video/avi'
- else t_kuozhanming
- end into t_filetype;
- end if;
- end if;
- return t_filetype;
- end|
- DELIMITER ;