Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: Function to handle non conforming last names

    I was asked about a function for putting names into proper case and I wrote up the following off the top of my head. Now though I am trying to figure out for non-conforming names. Did a couple quick searches and didn't see what I wanted. Here's what I have:

    Code:
    SELECT RTRIM(SUBSTR(T_NAME_FIRST,1,1)                               
        || LCASE(SUBSTR(T_NAME_FIRST,2,LENGTH(T_NAME_FIRST) -1))) || ' '
        || VALUE(NULLIF(T_MIDDLE_INIT,'') || '. ' ,'')                  
        || RTRIM(SUBSTR(T_NAME_LAST,1,1)                                
        || LCASE(SUBSTR(T_NAME_LAST,2,LENGTH(T_NAME_LAST) -1)))
    In my case this will change my stored name from DAVID W NANCE, into David W. Nance. Which is just what I want. It, also, takes into account if a middle initial is not provided so we do not have the '.'. Now, how do I get the following names converted properly???

    CHARLES K O'REILY with the above comes out as Charles K. O'reily where we want Charles K. O'Reily
    SCOTT MCCLINTOCK with the above comes out as Scott Mcclintock where we want Scott McClintock

    I'm thinking case statement on the second character of the last name, but wanted to see what other ideas you all might have. And a locate on ' ?

    Thanks.
    Dave

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I played with this a bit more last week prior to my weekend and have it where it now handles most non conforming names. With the one's above there were a few others to take into account as well, such as a hyphenated name or a name with multiple words such as Laura Van Horn. There will be exceptions to what I have written as you are going to find stuff that goes against the norm. I'm thinking about some logic for names with Mac??? like MacArthur, but then you could have Mack as well. My thoughts were if there are 2 vowels in the rest of the name to then upper case that first letter after Mac. It still won't be right in all cases, but probably closer than not doing it. Let me hear any ideas. Oh yeah the SQL grew a bit


    Code:
    SELECT
           CASE
                WHEN LOCATE('''',T_NAME_FIRST) = 2
                    THEN RTRIM(SUBSTR(T_NAME_FIRST,1,1)
                      || '''' || SUBSTR(T_NAME_FIRST,3,1)
    || LCASE(SUBSTR(T_NAME_FIRST,4,LENGTH(T_NAME_FIRST) - 3 )))
    
                WHEN LOCATE(' ',T_NAME_FIRST) >  2
                 AND LOCATE(' ',T_NAME_FIRST) <> LENGTH(RTRIM(T_NAME_FIRST))
                    THEN RTRIM(SUBSTR(T_NAME_FIRST,1,1)
    || LCASE(SUBSTR(T_NAME_FIRST,2,LOCATE(' ',T_NAME_FIRST)-1))
    || SUBSTR(T_NAME_FIRST,LOCATE(' ',T_NAME_FIRST)+1,1)
      || LCASE(SUBSTR(T_NAME_FIRST,LOCATE(' ',T_NAME_FIRST) + 2
                       ,LENGTH(T_NAME_FIRST) - (LOCATE(' ',T_NAME_FIRST)+1 )
    )   ))
    
                WHEN LOCATE('-',T_NAME_FIRST) >  2
                 AND LOCATE('-',T_NAME_FIRST) <> LENGTH(RTRIM(T_NAME_FIRST))
                    THEN RTRIM(SUBSTR(T_NAME_FIRST,1,1)
    || LCASE(SUBSTR(T_NAME_FIRST,2,LOCATE('-',T_NAME_FIRST)-1))
    || SUBSTR(T_NAME_FIRST,LOCATE('-',T_NAME_FIRST)+1,1)
      || LCASE(SUBSTR(T_NAME_FIRST,LOCATE('-',T_NAME_FIRST) + 2
                       ,LENGTH(T_NAME_FIRST) - (LOCATE('-',T_NAME_FIRST)+1 )
    )   ))
    
                WHEN LOCATE('''',T_NAME_FIRST) > 2
                 AND LOCATE('''',T_NAME_FIRST) <> LENGTH(RTRIM(T_NAME_FIRST))
                    THEN RTRIM(SUBSTR(T_NAME_FIRST,1,1)
    || LCASE(SUBSTR(T_NAME_FIRST,2,LOCATE('''',T_NAME_FIRST)-1))
    || SUBSTR(T_NAME_FIRST,LOCATE('''',T_NAME_FIRST)+1,1)
      || LCASE(SUBSTR(T_NAME_FIRST,LOCATE('''',T_NAME_FIRST) + 2
                       ,LENGTH(T_NAME_FIRST) - (LOCATE('''',T_NAME_FIRST)+1 )
    )   ))
                ELSE RTRIM(SUBSTR(T_NAME_FIRST,1,1)
                  || LCASE(SUBSTR(T_NAME_FIRST,2,LENGTH(T_NAME_FIRST) -1)))
           END  || ' '
        || VALUE(NULLIF(T_MIDDLE_INIT,'') || '. ' ,'')
    
        || CASE WHEN SUBSTR(T_NAME_LAST,1,2) = 'MC'
                   THEN RTRIM(SUBSTR(T_NAME_LAST,1,1)
                     || LCASE('C') || SUBSTR(T_NAME_LAST,3,1)
                  || LCASE(SUBSTR(T_NAME_LAST,4,LENGTH(T_NAME_LAST) -3)))
    
                WHEN LOCATE('''',T_NAME_LAST) =  2
                    THEN RTRIM(SUBSTR(T_NAME_LAST,1,1)
                      || '''' || SUBSTR(T_NAME_LAST,3,1)
    || LCASE(SUBSTR(T_NAME_LAST,4,LENGTH(T_NAME_LAST) - 3 )))
    
                WHEN LOCATE(' ',T_NAME_LAST) >  2
                 AND LOCATE(' ',T_NAME_LAST) <> LENGTH(RTRIM(T_NAME_LAST))
                    THEN RTRIM(SUBSTR(T_NAME_LAST,1,1)
    || LCASE(SUBSTR(T_NAME_LAST,2,LOCATE(' ',T_NAME_LAST)-1))
    || SUBSTR(T_NAME_LAST,LOCATE(' ',T_NAME_LAST)+1,1)
      || LCASE(SUBSTR(T_NAME_LAST,LOCATE(' ',T_NAME_LAST) + 2
                       ,LENGTH(T_NAME_LAST) - (LOCATE(' ',T_NAME_LAST)+1 )
    )   ))
    
    
                WHEN LOCATE('''',T_NAME_LAST) >  2
                 AND LOCATE('''',T_NAME_LAST) <> LENGTH(RTRIM(T_NAME_LAST))
                    THEN RTRIM(SUBSTR(T_NAME_LAST,1,1)
    || LCASE(SUBSTR(T_NAME_LAST,2,LOCATE('''',T_NAME_LAST)-1))
    || SUBSTR(T_NAME_LAST,LOCATE('''',T_NAME_LAST)+1,1)
      || LCASE(SUBSTR(T_NAME_LAST,LOCATE('''',T_NAME_LAST) + 2
                       ,LENGTH(T_NAME_LAST) - (LOCATE('''',T_NAME_LAST)+1 )
    )   ))
                WHEN LOCATE('-',T_NAME_LAST) >  2
                 AND LOCATE('-',T_NAME_LAST) <> LENGTH(RTRIM(T_NAME_LAST))
                    THEN RTRIM(SUBSTR(T_NAME_LAST,1,1)
    || LCASE(SUBSTR(T_NAME_LAST,2,LOCATE('-',T_NAME_LAST)-1))
    || SUBSTR(T_NAME_LAST,LOCATE('-',T_NAME_LAST)+1,1)
      || LCASE(SUBSTR(T_NAME_LAST,LOCATE('-',T_NAME_LAST) + 2
                       ,LENGTH(T_NAME_LAST) - (LOCATE('-',T_NAME_LAST)+1 )
    )   ))
                ELSE RTRIM(SUBSTR(T_NAME_LAST,1,1)
                  || LCASE(SUBSTR(T_NAME_LAST,2,LENGTH(T_NAME_LAST) -1)))
           END
    Dave

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id suggest you do the process and update the data so that you only need to run it once. You might need some other data element so that you aren't reprocessing the same same data each time. whehter you do that as a last updated on is upto you
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    that would be nice, but this will be a function. Have many different applications and separate table structures that have the name stored. I'm setting up the funtion to handle so that any of them can just pass in their selected columns as input. Though it would be nice if they were all stored properly to begin with.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •