mysqlsqldatabasesql-likedirname

Extract dirname from full path stored in database

How to extract only the dirname from full path which was stored in database.

I need to insert the scan files in another table which will filter only by dirname.

ex.

  1. /mnt/HD/HD_a2/Watch Me.mp3
  2. /mnt/HD/HD_a2/mymusic/sample.mp3
    • full path stored in tbl_files

INSERT INTO tbl_transcode (file_id) Select file_id from tbl_files where UPPER(file_path) Like CONCAT((Select source_path from tbl_transcode_folder where trancode_folder_id = 1 ),'%') and media_type = 'video'

But I need only dirname to insert in tbl_transcode, I think LIKE is not good to use.

Ex. I want to search only the dirname of this /mnt/HD/HD_a2/Watch Me.mp3 but when I used like /mnt/HD/HD_a2% it return me like this. which shown also other dirname.

  1. /mnt/HD/HD_a2/Watch Me.mp3
  2. /mnt/HD/HD_a2/mymusic/sample.mp3
Solution

  • If my understanding is correct, you are collecting the directory names for each file not the full path to the directory of the file.

    Following answer is specific to Oracle, where we make use of regexp_substr method.

    To get the full path with directory name we can use the following regular expression:

    substr(REGEXP_SUBSTR(c1,'^(.*/)*'),0,instr(REGEXP_SUBSTR(c1,'^(.*/)*'),'/',-1)-1)

    To get only the directory name we can use the following regular expression:

    replace(regexp_substr(substr(REGEXP_SUBSTR(c1,'^(.*/)*'),0,instr(REGEXP_SUBSTR(c1,'^(.*/)*'),'/',-1)-1),'/[a-zA-Z_0-9]+$'),'/','')

    Here c1 is a sample column i have used at my end.

    So, your updated query will be something like this:

    INSERT INTO tbl_transcode (file_id) 
Select file_id from tbl_files where UPPER(file_path) = upper(substr(REGEXP_SUBSTR(source_path,'^(.*/)*'),0,instr(REGEXP_SUBSTR(source_path,'^(.*/)*'),'/',-1)-1)) and media_type = 'video';

    You can also check this sqlfiddle

    Update (Answer specific to MySQL):

    We can make use of substring_index, locate & substring methods from MySQL to achieve the extraction of dir name.

    The substring_index method returns the substring from the number of occurrence of a character. Here the character of interest is /. So, invoking substring_index(c1,'/',-1) returns the file names from the given dir path.

    Next we can use locate to find the index of position of the file name and get the substring till beginning of file name using substr.

    To get the file names:

    SELECT substring_index(c1,'/',-1) FROM t1;

    To get the directory name with full path:

    SELECT substring(c1,1,locate(substring_index(c1,'/',-1),c1)-1) FROM t1;

    To get only the directory name:

    SELECT substring_index(substring(c1,1,locate(substring_index(c1,'/',-1),c1)-2),'/',-1) FROM t1;

    So, in the updated query for your case will be like the following:

    INSERT INTO tbl_transcode (file_id) Select file_id from tbl_files where UPPER(file_path) = upper(substring_index(substring(source_path,1,locate(substring_index(source_path,'/',-1),source_path)-2),'/',-1)) and media_type = 'video';

    Check this fiddle for a working example