update replace query with wildcard

[dfeg]

Hi
I'm currently an imageURL field in my database. All the url's are different and the image names are different lengths. I'm trying to run an update query to create a field called imageName.

In excel it would be fairly easy to just run find and replace using */ which deletes everything to the left of the / leaving me with just the file name at the end.

I've been at this for ages but apparently in access you can't do this an update query.

Is there any work around?

 

[KenHigg]

Something like the following may work:

right("MyString",(instrrev("MyString","/") + 1))

???

 

[dfeg]

Hi Ken,

Thanks for your response

an example of one of the URL's is
http://media.dorothyperkins.com/wcsstore/DorothyPerkins/images/catalog/17312402_normal.jpg

when i run your query it leaves me with a column full of "g"
I'm guessing because its the last letter of .jpg

 

[KenHigg]

What is the exact syntax you are using?

 

[Brianwarnock]

Not quite right Ken suspect a mere oversight

right([MyString],Len([MyString])-(instrrev([MyString],"/")))

Brian

 

[ajetrumpet]

dfeg,

maybe I can help you out a little bit more. this is what you're looking for:

right([your field],

len([your field]) -

instrrev([your field], "/"))

 

[Brianwarnock]

Snap! Adam

Brian

 

[MStef]

Still one way. Look at "DemoUpdateQryA2002.mdb"
Look at Table2, Query1, run query1.

 

[dfeg]

Hi sorry i had it slightly wrong the first time

I'm using

Right([imageName],(InStrRev([imageName],"/")+1))

Which starts off removing the left hand portions of the url and everytime you run it gets closer to the file name but it does not stop at the file name. It keeps removing until only the last letter is left.

There is no fixed amount of time for me to run the query as every url is a different amount of directories deep.

 

[Brianwarnock]

Hi sorry i had it slightly wrong the first time

I'm using

Right([imageName],(InStrRev([imageName],"/")+1))

Which starts off removing the left hand portions of the url and everytime you run it gets closer to the file name but it does not stop at the file name. It keeps removing until only the last letter is left.

There is no fixed amount of time for me to run the query as every url is a different amount of directories deep.

And what about the correction posted by myself and Adam?

Brian

 

[dfeg]

sorry lots of reply's in while i was typing my response, give me a minute to try them and i'll be back

 

[dfeg]

Brian and Adam you guys nailed it. I would never of got there by myself.

Thanks everyone for your help

 

[KenHigg]

Glad you have it working.

 

[Brianwarnock]

Brian and Adam you guys nailed it. I would never of got there by myself.

Thanks everyone for your help

What is important is that you understand how it works, also look up the full range of parsing functions
Instr, Instrrev
Right, Left, Mid
Len

I suspect Ken was thinking of the Mid as the second parameter is the starting position and if no length is supplied it defaults to rest of the string.

Brian

 

[KenHigg]

Yeah, I got a little ahead of myself and didn't think it all the way through...

 

[Brianwarnock]

Yeah, I got a little ahead of myself and didn't think it all the way through...

Getting muddled and changeing you mind part way through is a sign of old age Ken, I do it all the time, but you're just a youngster.

Brian

 

[ajetrumpet]

Getting muddled and changeing you mind part way through is a sign of old age Ken, I do it all the time, but you're just a youngster.

Brian

that's not all true brian. it's also a sign of ARROGANCE, as i have proven time and time again. don't hoard all the guilt you old guys!