SELECT REPLACE(file_path, SUBSTRING_INDEX(file_path,'/',-1),'') FROM ...
There may be other combinations of string functions which would do the same thing. See the manual for details <http://dev.mysql.com/doc/mysql/en/String_functions.html>.
I should point out that feeding a column through a function (or functions) like that is fine for the SELECT part, but undesirable in a WHERE clause, because once you feed the column to a function, you render any index on that column useless (for that query). If you will be choosing rows based on their paths, this will be a problem.
In my experience, it's best to keep path and filename separate. That is, break your current file_path column into two columns, path and filename. Then your three example rows would become
path filename =========================================== ================ /Volumes/External HD/aFolder/ aFile.pdf /Volumes/External HD/aFolder/ anotherFile.pdf /Volumes/External HD/aFolder/aChildFolder/ aThirdFile.pdf
That would have made both of your questions today much simpler to answer.
1) Change the path:
UPDATE your_table SET path = '/Volumes/External HD/aFolder/aChildFolder/' WHERE path = '/Volumes/External HD/aFolder/' ...
2) Get just the path
SELECT path FROM ...
3) Get the full file_path
SELECT CONCAT(path,filename) FROM ...
Changing your table should be easy, now that we've already figured out how to separate path from filename:
ALTER TABLE your_table ADD COLUMN path CHAR(N), ADD COLUMN filename CHAR(M);
Choose CHAR or VARCHAR and suitable values of M and N. Then,
UPDATE yourtable SET path = REPLACE(file_path, SUBSTRING_INDEX(file_path,'/',-1),''), filename = SUBSTRING_INDEX(file_path,'/',-1);
Inspect to make sure it worked, then
ALTER TABLE yourtable DROP COLUMN file_path;
Of course, you no doubt have code already in place which expects your current table definition, so you'll have to weigh the work of changing your code against the benefits of separating filename from path to make your own determination of whether or not it's worth it.
Michael
John Mistler wrote:
Wow! That works. Thanks a bunch! While we're at it, I have one more hang-up:
Is there a (DISTINCT?) statement that will select the distinct folder path(s) of every entry in the table?
For instance, if I have three files:
"/Volumes/External HD/aFolder/aFile.pdf" "/Volumes/External HD/aFolder/anotherFile.pdf" "/Volumes/External HD/aFolder/aChildFolder/aThirdFile.pdf"
the query would return:
"/Volumes/External HD/aFolder/" "/Volumes/External HD/aFolder/aChildFolder/"
Any ideas?
Thanks again,
John
on 9/1/04 8:03 PM, Michael Kruckenberg at [EMAIL PROTECTED] wrote:
You can use the replace string function:
update table set file_path=replace(file_path,'aFolder','aFolder/aChildFolder');
In each update aFolder will be replaced by the new path.
John Mistler wrote:
I have a column that holds hard disk file location info such as: "/Volumes/External HD/aFolder/aFile.pdf" "/Volumes/External HD/aFolder/anotherFile.pdf" etc. . . (many files located in the same folder)
Can anyone suggest a single statement (or multiple) that would update every file location located in this same folder to another location, say:
"/Volumes/External HD/aFolder/aChildFolder/aFile.pdf" "/Volumes/External HD/aFolder/aChildFolder/anotherFile.pdf" etc . . . ?
Thanks,
John
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]