Expressions to extract portion of a text string in Access

[Hello1]

Hello,

Im trying to extract some text from a text field.
For example if the value is " John" I need the expression to return me "John" or if the value is " John co. sth" I also need it to return me "John".
I tried this:

IIf(InStr(InStr(" John co. sth"," ")+1," John co. sth"," ") _
<>0, Right(" John ",Len(" John co. sth")-InStr(InStr(" John co. sth"," ")+1, _
" John co. sth"," ")),Right(" John co. sth",Len(" John co. sth")-InStr(" John co. sth"," ")))

For " John" it returns the right result, but for " John co. sth" I get "co. sth".

Another thing I need is:
For "John" to return same "John" and for "John co. sth" to return also "John".

If I cant make one expression for all of this Im going to do it in VBA probably anyway so I would say something like if the first character is " " then the expression for the first example I gave, if not then for the second example.

I hope I have been clear enough
Thanks!

 

[theDBguy]

Hi. See if the Regular Expression article on my blog could help you with this.

 

[gemma-the-husky]

result = Split(Trim(expn), " ")(0)

this removes any leading/trailing spaces, and then return the first word in the string (splitting the full string on space characters)

 

[rpeare]

iif(instr([Fieldname], <SearchString>), mid([Fieldname], instr([Fieldname]), len([Fieldname]), null)

would return the search string if exists in the field, otherwise return a null value

 

[Gasman]

Shouldn't that be ?

iif(instr([Fieldname], <SearchString>), mid([Fieldname], instr([Fieldname],<SearchString>), len(SearchString)), null)

 

[rpeare]

yes, I botched the second instr statement

 

[Gasman]

yes, I botched the second instr statement

Well initially I thought just the length parameter of the function was referring to the incorrect variable, but as I was altering it, I noticed the second parameter of the second Instr was missing.

 

[Hello1]

Thanks guys. For the first case I added just Trim to get rid of that extra space on begging and for the second one:

Left([FieldName], InStr(1, [FieldName] & " ", " ") - 1)

 

[Galaxiom]

iif(instr([Fieldname], <SearchString>), mid([Fieldname], instr([Fieldname]), len([Fieldname]), null)

would return the search string if exists in the field, otherwise return a null value

Another variation is to concatenate the search string to the end of the expression that is being searched in case it isn't contained in the searched expression.

I use this where there is an optional dot followed by more characters at the end of the expression and I don't want the dot and subsequent characters.

The resulting expression is simpler than including two different versions of the search for inputs with and without the dot.

For example:

Left([fieldname], Instr([fieldname] & ".", ".") - 1)

rather than:

Iif(Instr([fieldname],"."), Left([fieldname], Instr([fieldname],".") - 1),[fieldname])

 

[Galaxiom]

Thanks guys. For the first case I added just Trim to get rid of that extra space on begging and for the second one:

Left([FieldName], InStr(1, [FieldName] & " ", " ") - 1)

BTW The first parameter of InStr() is optional.

AFAIK it is the only function where an optional parameter is not after all required parameters.

I have occasionally contemplated how the function definition is built and never worked it out. I don't think user defined functions can be written like this at all.