Re: UPDATE string segment?

Thu, 02 Sep 2004 07:46:44 -0700

That's a little trickier. You can use SUBSTRING_INDEX() to split a string on the Nth '/', but N will vary with path length. With N = -1, though, we'll get the piece after the last '/' (the filename). Then we could replace that with nothing in the original string. That is, something like this (I'm assuming your column is named file_path):

  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]

Reply via email to