Proper Case formula

    Date: 03/18/05 (SQL Server)    Keywords: no keywords

    Hello …. I’m new here.  Apologies if this is inappropriate, but I am sufficiently frustrated so I thought I would ask a 
    collective intelligence. I'm working on a formula I have in a Crystal report. Actually, it’s a formula I found online, and it   *almost* suits my needs. Let me add that I’m working with CR 8.5 (and need to in order for it to remain compatible with
    another system at my work), so I cannot utilize the Proper Case function introduced in CR 9. I’ve also tried downloading
    the UFL for Title Case, and that turned out to be more trouble than help. So I’m back to trying to perfect the formula.

    The formula looks like this:
    ⁄⁄CONVERT FIELD TO PROPER CASE

    Local Numbervar x;
    Local Numbervar i;
    Local Stringvar Holder;
    Local Stringvar Final;
    Local Stringvar Proper;
    Local Stringvar vString;

    vString := trim({DBfield});
    x := length(vString);

    If x >= 1 then
    (
    For i := 1 to x do
    (
    Select vString[i]
    Case " ":
    (
    (If Final in ["P.O.", "U.S.", "C⁄O", "PO"] then
    (Final := Uppercase(Final);
    Proper := Proper + Final + " ";
    Holder := "";
    Final := "")
    Else
    Holder := Uppercase(Final)[1];
    Final := Final[2 to (i-1)];
    Proper := Proper + Holder + Final + " ");
    Holder := "";
    Final := ""
    )

    Default:

    (
    Holder := Holder + Lowercase(vString)[i];
    Final := Final + Holder;
    Holder := ""
    );

    );

    Holder := Uppercase(Final)[1];
    Final := Final[2 to (i-1)];
    Proper := Proper + Holder + Final;
    Holder := "";
    Final := "";
    x := 0;
    i := 0;
    Proper
    )
    Else ""

     
    This successfully takes a name that is formatted like so: BLOW,JOE 
    and turns it into this: Blow,joe  
     
    What I need it to do is take the comma into consideration, add a space after the comma, and capitalize the first 
    name as well, like so: Blow, Joe
     
    If there's a middle initial, it's be great if there was a period after it, but I'm not that concerned about that at the moment. 

    Any chance someone knows how to fix this?

     

    Source: http://www.livejournal.com/community/sqlserver/21891.html

« A query.. || Access to SQL Server Question »


antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home