Rearranging a string??? Mid/Instr functions...

[Apollo6]

I have a field that is a persons name:
Example1: John X. Doe
or
Example2: Jane Doe

Sometimes the field is like example1 sometimes like example2. How can I write the code to where the results are:

New Example1: Doe, John X.
Or
New Example2: Doe, Jane

I wrote the code that handles Example2 but when it hits a record like Example1 the results are: X. Doe, John.

What I need, again, is for it to be: Doe, John X.

Thanks for your help!!!

 

[PaulTEG]

Hi Appollo,

Count the number of spaces in your string using InStr passing the resulting value as the start, and the subsequent result and so on.

Then use the result prior to getting 0 as the location of your surname.

Have you thought about surnames such as Mc Andrews, O Donahue, Van Doren, are there suitable validation rules on the data coming in?

Hope that helps

;P

 

[Apollo6]

No, there are not validation rules on the data coming in and I am not able to change the data.

My code so far is:

MID({APIBH.TEXTRMIT},instr(1,{APIBH.TEXTRMIT}," &quot+1)
+", "+MID({APIBH.TEXTRMIT},1,instr(1,{APIBH.TEXTRMIT}," &quot-1)

This gives me the X. Doe, John and Doe, Jane scenario. I am still looking/working on your last post.

 

[Apollo6]

Just a note, this is actually a formula box in Crystal Reports... So my syntax looks a little different from standard SQL.

 

[PaulTEG]

Hi Apollo,

You're not the first, you won't be the last ...
Don't wreck your head, the surnames don't conform to any validation rules, and as such don't qualify for exception code unless certain assumptions have been stated by either you or others.

Is there a possibility that earlier in the upstream processes that the first name(s) & surname as well as middle initial are stored as seperate atoms. If not, it might be worthwhile exploring this.

If you only get Garbage In, you can only get Garbage Out.

If its something you want to persist with, you may wish to consider seperate rules for each exception case, expect nosebleeds

;P

PS if there's a string.reverse function in Crystal, it might make your life a lot easier

 

[Apollo6]

Crystal does have the strReverse() function. I have never used this before, but it literally reversed the string. So now my examples are as follows with using the function as is:

Example1: eoD .X nhoJ
or
Example2: eoD enaJ

Needing-

Example1: Doe, X. John
Example2: Doe, Jane

I'm not sure if this is better or worst...How can I rewrite the strings once they have been "Reversed" to read correctly???

Still looking...

 

[Apollo6]

FYI--
This should really be for a Crystal Reports Forum but the following code gets me there:

'See if there is a "." in the string
Local NumberVar ipos1:=InStr ({APIBH.TEXTRMIT},"."

'Conditional Formatting depending on the variable above
If ipos1 < 1 Then
MID({APIBH.TEXTRMIT},instr(1,{APIBH.TEXTRMIT},&quot; &quot+1)
+&quot;, &quot;+MID({APIBH.TEXTRMIT},1,instr(1,{APIBH.TEXTRMIT},&quot; &quot-1)
Else
MID({APIBH.TEXTRMIT},instr(1,{APIBH.TEXTRMIT},&quot;.&quot+1)
+&quot;, &quot;+MID({APIBH.TEXTRMIT},1,instr(1,{APIBH.TEXTRMIT},&quot; &quot-1)

Thanks for the suggestions though!!!

 

[PaulTEG]

OK there's a string rev function in Crystal

If you use the formula field to calc the first space in the reversed field (may want to look for caM, cM, naV, 'O etc, but leave that till later)

When processing the Name field, use
mid(str, length(str) - firstspace from strRev, len(str)) & &quot; &quot; & left(str, length(str)-firstspace from strRev)

Hope this makes some form of sense, it's l8 & time 4 TV

Cheers (more beer)
;P