extracting text from a string

[retygh45]

I'm using CR10, and trying to parse data from the Exchange Message tracking logs, which are very messy. One field, the receipient, often looks like this:

/O=CELOTEX/OU=WILMINGTON/CN=RECIPIENTS/CN=Administrator

I'm trying to figure out a way to get just "Administrator" out of it, but I'm stuck. I was going to trim around "CN=", but there's more than one instance of it.

Any help finding last, or rightmost instance of "CN=", then just displaying everything after that last "CN=" is greatly appreciated, thanks!

 

[synapsevampire]

If it's always Administrator the solution is much simpler, simply do an INSTR() for it and if > 0 show Administrator.

As for the rightmost CN=, use INSTRREV

mid({table,field}, InstrRev ({table,field}, "CN=")+3)

This assumes that it's the last thing in the field as well. If not, you might store the above in a variable and then use a instr for the next space.

-k

 

[retygh45]

Thanks for the help, but it's not printing anything, the formula is blank.

Unfortunately, it's not always that string. It seems for inter-office email that never leaves the mail server, it's this string thing, otherwise it's a normal looking address.

So, I'm actually trying to test for the string like this:

If LEFT({LogsTable.RcptAddress},10) = "/O=CELOTEX" THEN
mid({LogsTable.RcptAddress}, InstrRev ({LogsTable.RcptAddress}, "CN=")+3)
ELSE
{LogsTable.RcptAddress}

I think that should be ok, but I can't seem to get the InsrRev working correctly.

Thanks!

 

[synapsevampire]

Why are you using this: "If LEFT({LogsTable.RcptAddress},10) = "/O=CELOTEX" THEN"?

Tyr just the mid at first to test. The add in other conditions.

-k

 

[retygh45]

I was testing for the beginning of the string. Some values in the field are regular addresses:

joe@something.com

If it's not a regular address like this, it's an inter-office email, and looks like this:

/O=CELOTEX/OU=WILMINGTON/CN=RECIPIENTS/CN=SOMEBODY

So I was trying to test the left part of the field to see if it starts like this "/O=CELOTEX" then it would find the rightmost "CN=" and display just "SOMEBODY" on the account. If not, I assume it's a regular address, and just do nothing but display it.

But that doesn't matter, b/c when I just display the name with this:

mid({LogsTable.RcptAddress}, InstrRev ({LogsTable.RcptAddress}, "CN=")+3)

It doesn't display anything. Thanks again for your help!

 

[synapsevampire]

What does this return:

InstrRev ({LogsTable.RcptAddress}, "CN=")

If nothing, than it doesn't make sense.

Try this:

instrrev("/O=CELOTEX/OU=WILMINGTON/CN=RECIPIENTS/CN=SOMEBODY","CN=")

-k

 

[retygh45]

I'm probably not explaining what I'm trying to do very well. It's just the very last part after the last "CN=" which is the user name I'm trying to display.


InstrRev ({LogsTable.RcptAddress}, "CN=")

returns a number: 0.00

Not sure why that is. Thanks again for you help though, I really appreciate it.

 

[synapsevampire]

It should return zero for some rows, but NOT if the string doesn't exist in that record.

Place the formula in the details alongside the field so that you can verify that it returns zero, btw, the formula must be in the details in case you didn't know.

-k

 

[synapsevampire]

Rather, but not if the string exists.

Try a instr() function as well.

-k

 

[retygh45]

Ok, got it, thanks so much!

One more question, I'm trying to do something similiar on a different field:

This field, not always, but often has a " - " (space, dash, space) on the end of it, and I'm trying to strip it off.

So it's like: "somedude@email.com - " And I just want to compare it to "somedeude@email.com"

Thanks again!

 

[retygh45]

This is what I'm trying, but it's printing the whole field, including the " - "

if right({LogsTable.Sender},3) = " - " then
left({LogsTable.Sender},instrrev({LogsTable.Sender}," - ")-1) else
{LogsTable.Sender}

 

[lbass]

Sounds like you might have more than one space at the end. Try:

if right(trim({table.string}),2) = " -" then
left(trim({table.string}),instrrev({table.string}," -")-1) else
table.string

Or you could use:

left(trim({table.string}),len(trim({table.string}))-2)

-LB

 

[retygh45]

Awesome, thanks so much, that works great, I really appreciate it.

One question: When I created the report, I had both fields in the details, just so I can compare the 2 fields. But when I remove the original field from the report, the " - " appears back in my formula field, when I put the original field back on the report, it disappears.

How can I get the formula to work without the original field being on the report?

Thanks again

 

[lbass]

That's very odd behavior. The "original field" is actually a field, not a formula or SQL expression, right?

-LB

 

[synapsevampire]

As LB stated, this is very strange.

I would try applying the service pack first, it just doesn't makie sense that it would matter regardless of whether it's a formula or a SQL Expression, if your formula is based on it, it would still be used whether on the canvas or not.

-k

 

[retygh45]

Sorry, I had something else in there. I can remove the original field from the canvas, but the formula isn't working. I've got this:

if right(trim({LogsTable.Sender}),2) = " -" then
left(trim({LogsTable.Sender}),instrrev({LogsTable.Sender}," -")-1) else
{LogsTable.Sender}

But it's still printing the dash on the end. I can copy and paste the field into a text file, and it's definitely just a " -", so the right 2 should do it.

Thanks again!

 

[lbass]

left(trim({table.string}),len(trim({table.string}))-2)

...should work.

-LB

 

[synapsevampire]

If it's just a dash at the end, try:

trim(replace({table.field},"-",""))

Simpler and does the same thing.

-k

 

[synapsevampire]

Since it's an email account, skip that, the address might contain a dash.

Go with LBs solution or use a SQL Expression.

-k