REGEXP and MySQL Select: Retreiving What I'm Looking For?

Hi there

If I have a "Filename" column in a database, and I want to grab the
EXTENSION of this filename... can I do it in a query using REGEXP or
something similar?

I've got this going:

"SELECT f.FileID,f.FileName,f.FileName REGEXP '.+\.([^.]+)$' AS FileExt
FROM ProjFiles f
WHERE f.FolderID=584"

But my resulting "FileExt" column just shows a '1' (meaning that yes, a
file extension was found).

Instead of getting a "yes/no" answer from my regexp, can I actually get
the file extension itself?

Thanks!!

> If I have a "Filename" column in a database, and I want to grab the
> EXTENSION of this filename... can I do it in a query using REGEXP or
> something similar?
>
> I've got this going:
>
> "SELECT f.FileID,f.FileName,f.FileName REGEXP '.+\.([^.]+)$' AS FileExt
> FROM ProjFiles f
> WHERE f.FolderID=584"
>
> But my resulting "FileExt" column just shows a '1' (meaning that yes, a
> file extension was found).
>
> Instead of getting a "yes/no" answer from my regexp, can I actually get
> the file extension itself?

SELECT f.FileID,f.FileName,right(filename,instr(reverse(f ilename),'.')-1)
as FileExt
FROM ProjFiles f
WHERE f.FolderID=584


Regards
Dimitre

"Radoulov, Dimitre" <cichomitiko@gmail.com> wrote in
news:454bc450$0$49201$14726298@news.sunsite.dk:

>> Instead of getting a "yes/no" answer from my regexp, can I actually
>> get the file extension itself?
>
> SELECT
> f.FileID,f.FileName,right(filename,instr(reverse(f ilename),'.')-1) as
> FileExt FROM ProjFiles f
> WHERE f.FolderID=584
>
>
> Regards
> Dimitre


Thank you very much Dimitre!

Good Man wrote:
> Hi there
>
> If I have a "Filename" column in a database, and I want to grab the
> EXTENSION of this filename... can I do it in a query using REGEXP or
> something similar?
>
> I've got this going:
>
> "SELECT f.FileID,f.FileName,f.FileName REGEXP '.+\.([^.]+)$' AS FileExt
> FROM ProjFiles f
> WHERE f.FolderID=584"
>
> But my resulting "FileExt" column just shows a '1' (meaning that yes, a
> file extension was found).
>
> Instead of getting a "yes/no" answer from my regexp, can I actually get
> the file extension itself?
>
> Thanks!!

That looks long-winded. Why not have a look at REVERSE