extract texst after certain word (1 Viewer)

[oostijen]

extract text after certain word

Hi, i'd like to extract the text after a certain word. (itemnumber)

Example: themeplanner=mode&itemnumber=12345678&context=beauty

In the abvove example the returned text should be: 12345678.

Anybody any ideas?

 

[Twin]

Hi There,
try following the link, the subject was covered pretty tight...
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=11558

 

[oostijen]

I wrote the following query:

Mid (varname, InStr(1,QueryString, "itemnr=") ,18 ) AS item,

This works, however now the results include the word "itemnr="

Is it possible to make a query that returns only the text after "itemnumber="

 

[antomack]

Forgive the length of the post but decided I'd best put in some sort of explanation with the expression. The following expression will give you everything after "itemnumber=" or any particular text string you want to find text after, if it exists in the string.

Expr1: IIf(InStr(1,[YourTextField],[Search String])>0,Mid([YourTextField],InStr(1,[YourTextField],[Search String])+Len([Search String]),Len([YourTextField])+1-(InStr(1,[YourTextField],[Search String])+Len([Search String]))),[YourTextField])

To help you follow what is going on here read the details below.
When you run the query it will prompt you for the string 'Search String' that you want all text after. Then essentially it is broken as follows
1 Check does the 'Search String' exist
IIf(InStr(1,[YourTextField],[Search String])>0
2 If exists then it proceeds to find the text after your 'Search String'.
Mid([YourTextField],InStr(1,[YourTextField],[Search String])+Len([Search String]),Len([YourTextField])+1-(InStr(1,[YourTextField],[Search String])+Len([Search String])))
3 If it doesn't exist then it just returns the text from your field as was.
[YourTextField]

Part 2 is finding the text within your field that is after the 'Search String'. 'Search String' is found (starts) at the position found by
InStr(1,[YourTextField],[Search String]),
so to find the first position after your 'Search String' you need to add on the length of your 'Search String'.
InStr(1,[YourTextField],[Search String])+Len([Search String])
To specify the length of the remaining string it's basically the overall length minus the length of the text up to the end of your 'Search String'.
Len([YourTextField])+1-(InStr(1,[YourTextField],[Search String])+Len([Search String]))

For the example you gave of

themeplanner=mode&itemnumber=12345678&context=beauty

the above expression will return

12345678&context=beauty

Further manipulation could be done to just get the numbers '12345678'