Professional Web Applications Themes

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

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!!...

  1. #1

    Default 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!!


    Good Guest

  2. #2

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

     

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


    Regards
    Dimitre



    Radoulov, Guest

  3. #3

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

    "Radoulov, Dimitre" <com> wrote in
    news:454bc450$0$49201$sunsite.dk:
     
    >
    > SELECT
    > f.FileID,f.FileName,right(filename,instr(reverse(f ilename),'.')-1) as
    > FileExt FROM ProjFiles f
    > WHERE f.FolderID=584
    >
    >
    > Regards
    > Dimitre[/ref]


    Thank you very much Dimitre!


    Good Guest

  4. #4

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


    Good Man wrote: 

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

    strawberry Guest

Similar Threads

  1. MySQL subquery in select
    By Kasper K in forum MySQL
    Replies: 6
    Last Post: July 12th, 02:24 PM
  2. Inserting, updating, and retreiving a file as blob
    By MindHunter in forum Coldfusion Database Access
    Replies: 1
    Last Post: September 6th, 05:34 PM
  3. Function for retreiving datatype
    By Brendan Jurd in forum PostgreSQL / PGSQL
    Replies: 13
    Last Post: January 11th, 09:00 AM
  4. Mysql select query with php
    By Benjamin Dickgießer in forum PHP Development
    Replies: 7
    Last Post: November 20th, 10:57 PM
  5. Valid MySQL database/table/column name regexp
    By Martin Lucas-Smith in forum PHP Development
    Replies: 3
    Last Post: August 15th, 07:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139