Find and Rename in SQL

  • Thread starter Thread starter HWhite
  • Start date Start date
H

HWhite

I have an access field that was a hyperlink, now converted to text. It is
messy. Here's an example of what it looks like after converted to text.

020411-1568-07-25-08-Depo
Docs#E-File%20Indices/1568/Depo.%20Docs%20from%20S.Haney%20file/020411-1568-07-25-08-Depo%20Docs.PDF#

I need to do the following and I think a SQL statement is what I need but
don't know enough to do it without help.

I need to find and delete anything that exists before /1568
I need to find and rename /1568 to 1568
I need to find and rename / to \
I need to find and rename %20 to a space
I need to find and rename 1568\ to t:\
I need to find and delete #

The end result should look like this

t:\1568\Depo. Docs from S.Haney file\020411-1568-07-25-08-Depo Docs.PDF

I then need to convert it back to a hyperlink.

Thank you for your help.
 
I have an access field that was a hyperlink, now converted to text. It is
messy. Here's an example of what it looks like after converted to text.

020411-1568-07-25-08-Depo
Docs#E-File%20Indices/1568/Depo.%20Docs%20from%20S.Haney%20file/020411-1568-07-25-08-Depo%20Docs.PDF#

I need to do the following and I think a SQL statement is what I need but
don't know enough to do it without help.

I need to find and delete anything that exists before /1568
I need to find and rename /1568 to 1568
I need to find and rename / to \
I need to find and rename %20 to a space
I need to find and rename 1568\ to t:\
I need to find and delete #

The end result should look like this

t:\1568\Depo. Docs from S.Haney file\020411-1568-07-25-08-Depo Docs.PDF

I then need to convert it back to a hyperlink.

Thank you for your help.
Click to expand...

Try using a calculated field in the query:

NewFile: "T:" & Replace(Replace(Mid([fieldname], InStr([fieldname], "/"), "/",
"\"), "%20", " ")
 
I copy and pasted your text into the "field" area of a new column in the
query. I replaced [fieldname] to [link to file] which is the actual field
name in the table.

Trying to navigate away from the field, I get "the expression you entered
has a function containing the wrong number of arguments." and puts the cursor
in between the close paren and comma after \

how can I fix this... and I assume you only gave me a partial example and I
would need to fill in the rest of the things that need to be replaced as I
mentioned in my original message?

thanks.

John W. Vinson said:
I have an access field that was a hyperlink, now converted to text. It is
messy. Here's an example of what it looks like after converted to text.

020411-1568-07-25-08-Depo
Docs#E-File%20Indices/1568/Depo.%20Docs%20from%20S.Haney%20file/020411-1568-07-25-08-Depo%20Docs.PDF#

I need to do the following and I think a SQL statement is what I need but
don't know enough to do it without help.

I need to find and delete anything that exists before /1568
I need to find and rename /1568 to 1568
I need to find and rename / to \
I need to find and rename %20 to a space
I need to find and rename 1568\ to t:\
I need to find and delete #

The end result should look like this

t:\1568\Depo. Docs from S.Haney file\020411-1568-07-25-08-Depo Docs.PDF

I then need to convert it back to a hyperlink.

Thank you for your help.
Click to expand...

Try using a calculated field in the query:

NewFile: "T:" & Replace(Replace(Mid([fieldname], InStr([fieldname], "/"), "/",
"\"), "%20", " ")
Click to expand...
 
I copy and pasted your text into the "field" area of a new column in the
query. I replaced [fieldname] to [link to file] which is the actual field
name in the table.

Trying to navigate away from the field, I get "the expression you entered
has a function containing the wrong number of arguments." and puts the cursor
in between the close paren and comma after \

how can I fix this... and I assume you only gave me a partial example and I
would need to fill in the rest of the things that need to be replaced as I
mentioned in my original message?
Click to expand...

Must have been a typo on my part: let's see... yep missing parenthesis:

NewFile: "T:" & Replace(Replace(Mid([link to file], InStr([link to
file],"/")), "/","\"), "%20", " ")

Breaking up the logic, I'm using

InStr([link to file],"/")

to find the position of the first / in the filename;

Mid([link to file], <that position>)

to substring everything after and including the first / into a string;

and two nested Replace calls on that string to replace / with \ and %20 with a
blank.
 
thank you very much

Also, the logic breakdown helped me add the remaining renames I needed.
Thanks for including that.

John W. Vinson said:
I copy and pasted your text into the "field" area of a new column in the
query. I replaced [fieldname] to [link to file] which is the actual field
name in the table.

Trying to navigate away from the field, I get "the expression you entered
has a function containing the wrong number of arguments." and puts the cursor
in between the close paren and comma after \

how can I fix this... and I assume you only gave me a partial example and I
would need to fill in the rest of the things that need to be replaced as I
mentioned in my original message?
Click to expand...

Must have been a typo on my part: let's see... yep missing parenthesis:

NewFile: "T:" & Replace(Replace(Mid([link to file], InStr([link to
file],"/")), "/","\"), "%20", " ")

Breaking up the logic, I'm using

InStr([link to file],"/")

to find the position of the first / in the filename;

Mid([link to file], <that position>)

to substring everything after and including the first / into a string;

and two nested Replace calls on that string to replace / with \ and %20 with a
blank.
Click to expand...
 
Back
Top