Extracting everything to the right of a value

[smmedeiros]

I'm trying to extract everything to the right of a particular field value
example:
1 of 2 servers to replace the existing Sterling Gateway and backup server PATCHING SCHEDULE 7/13/12 5PM -7/14/12 5AM [2AM-5AM reboot window]

I want my results to be:
PATCHING SCHEDULE 7/13/12 5PM -7/14/12 5AM [2AM-5AM reboot window]

content before the keyword "PATCHING" can be of variable length and characters
content after the keyword "PATCHING" can be of variable length & characters.
Common throughout will be keyword "PATCHING"

I'm familiar with the use of extractstring, however, that requires a start and ending point. I want everything after the keyword PATCHING.

Appreciate any/all ideas.
Thanks

 

[fisheromacse]

i do not have crystal in front of my so apologize for any mistakes in advance.
i think you should be able to extract the data you desire by first determining the starting location of the work "PATCHING", second by determining the length of the data in the field, and lastly by performing a MID on the field with the numbers determined in steps 1 and 2.
something like below:

//{@formula}
numbervar loc := instr({table.field},"PATCHING")-1;
numbervar lgt := len(TRIM({table.field});
MID({table.field},loc,lgt)

 

[smmedeiros]

Awesome memory.. just had to add another paranthesis on end of 2nd line and it worked perfectly.
Thanks for the help!

 

[smmedeiros]

Thought the above solution would be the end of it.. however, applying the formula and running it with a full database population, i'm hitting on this erorr:
"Start position is less than 1 or not an integer"
Any tips to get past this error?

 

[kray4660]

I believe instr returns the starting position of the found string, so if PATCHING is at the beginning of the string, then it will return a zero for loc, so I think the line numbervar loc := instr({table.field},"PATCHING")-1; should be changed to numbervar loc := instr({table.field},"PATCHING")+8;

This has not been tested.

 

[pmax9999]

You could avoid the use of the variable altogether, and use this formula:

If Instr({@table.field},'PATCHING') > 0
Then Mid({@table.field}, Instr({@table.field},'PATCHING'))

 

[smmedeiros]

Thanks for the continued ideas. PATCHING keyword is not always at the beginning of the string. Unfortunately, it can be located anywhere in the field. I updated the formula as suggested with the +8 and now i'm getting some unwanted content. here is a few examples:
original field:
Citrix web interface
IPS approved Microsoft patching windows 2010 (see maintenance window for further details):
- 20 March
- 19 June
- 25 September
- 11 December

Desired results:
Citrix web interface
patching windows 2010 (see maintenance window for further details):
- 20 March
- 19 June
- 25 September
- 11 December

Current results with formula suggested above (it cuts off the first word 'citrix')
web interface
IPS approved Microsoft patching windows 2010 (see maintenance window for further details):
- 20 March
- 19 June
- 25 September
- 11 December

so, as stated in the original entry, any/all words to the right of the keyword "patching" is my desire.

If this is not possible, i will work with my customers to put this content between 2 distinct parameters i can then is extractstring formula.

Thanks in advance for any help.

 

[kray4660]

It shouldn't be difficult unless the data is too inconsistent. We apparently are missing something. I am confused with the line 'IPS approved Microsoft patching windows 2010 (see maintenance window for further details):', it seems that at least one of the formulas would return ' windows 2010 (see maintenance window for further details):' (assuming case insensitivity).

 

[smmedeiros]

Thank you both for your ideas. I've managed to get closer to my desired results with pmax9999 suggestion. Appreciate the help.

 

[pmax9999]

My suggestion would have worked, regardless of where the word "PATCHING" appeared in the string. The only possibe tweak required may have been to deal with case issues if your database is case sensitive. This slight change will do the trick:

If Instr(UPPERCASE({table.field}),'PATCHING') > 0
Then Mid({table.field}, Instr(UPPERCASE({table.field}),'PATCHING'))

If it isn't quite doing what you want, please let us know how the results differ from what you want.

Cheers