Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

need update query to split name field into first last mi

Posted on 2009-04-13
18
757 Views
Last Modified: 2013-11-27
im changing the fields of a table to split the name field into first last mi fields.  i want to get the first letter upper case.  not sure how i do that.  also not sure how to split the name also.  can anybody help me with this?
0
Comment
Question by:sumhungl0
  • 8
  • 6
  • 3
  • +1
18 Comments
 
LVL 35

Expert Comment

by:YZlat
ID: 24130635
If they are separate dby spaces, something like this should work:

SELECT SubString(Name, 1, CharIndex(' ', Name)-1)
0
 

Author Comment

by:sumhungl0
ID: 24130706
does SubString go into criteria?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 24130724
what do you mean?


Also can you gvie me a sample string format for your field?  Ex: John Doe, M
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:sumhungl0
ID: 24130783
oh yea, sorry.  right now the field is pName and the format of names already in the table are (Last First Mi).  the new fields i made are fName for first name, lName for last, mName for middle.
0
 

Author Comment

by:sumhungl0
ID: 24130816
the above sql gives me an error.
Undefined function 'SubString' in expression.
0
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 24130968
Is there a comma after the last name in pName?
0
 

Author Comment

by:sumhungl0
ID: 24130987
no, its Last SPACE First SPACE Mi
0
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 24131104
Change "names" to name of your table:


SELECT pname, LEFT(MID(pName,INSTR(1,TRIM(pName),' ',1)+1),INSTR(1,TRIM(MID(pName,INSTR(1,TRIM(pName),' ',1)+1)),' ',1)-1) AS fname, MID(MID(pName,INSTR(1,TRIM(pName),' ',1)+1),INSTR(1,TRIM(MID(pName,INSTR(1,TRIM(pName),' ',1)+1)),' ',1)+1) AS mi
FROM [names];
0
 

Author Comment

by:sumhungl0
ID: 24131135
ok that has the first and middle working, no last.  ill see if i can figure that out.  any way to get the first letter capitalized?
0
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 24131155
Use UCASE:

SELECT pname, LEFT(MID(pName,INSTR(1,TRIM(pName),' ',1)+1),INSTR(1,TRIM(MID(pName,INSTR(1,TRIM(pName),' ',1)+1)),' ',1)-1) AS fname, UCASE(MID(MID(pName,INSTR(1,TRIM(pName),' ',1)+1),INSTR(1,TRIM(MID(pName,INSTR(1,TRIM(pName),' ',1)+1)),' ',1)+1)) AS mi
FROM [names];
0
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 24131172
Sorry, that capitalizes whole word. Use UCASE(LEFT(word,1)) & LCASE(MID(word,2)) to cap first letter only.
0
 

Author Comment

by:sumhungl0
ID: 24131208
can you put that into your example, i dont know where to put ucase and lcase?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 24131262
oops, Substring is for SQL Server, I didn't notice your was Access db
0
 
LVL 11

Expert Comment

by:Missus Miss_Sellaneus
ID: 24131263
For "word" in my UCASE excample, substitute the whole expression for a field (the whole part before "AS"). Sorry, don't have time to put it together, I have to leave for work now.
0
 

Author Comment

by:sumhungl0
ID: 24131436
yea ive got this all messed up, ill wait till you get time.  thanks miss
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24131572
You can use the UDF below for this...

SELECT pname, RegExpFind(pname, "[A-Z][a-z]{0,}", 1, True) AS LName, RegExpFind(pname, "[A-Z][a-z]{0,}", 2, True) AS FName,
      RegExpFind(pname, "[A-Z][a-z]{0,}", 3, True) AS MName
FROM SomeTable

If there is no middle name, then that gets an empty string.

Note: this will fail if any of the names has two words (Jill St. John), have punctuation (Bill O'Reilly, Lotho Sackville-Baggins),
etc.

It can also fall down if you have titles/honorifics/Jr Sr III etc sprinkled in the wrong places.

Name parsing is actually much more complicated than it looks :)
Function RegExpFind(LookIn As String, PatternStr As String, Optional Pos, _
    Optional MatchCase As Boolean = True) 
    ' For more info see: http://vbaexpress.com/kb/getarticle.php?kb_id=841 
    ' This function uses Regular Expressions to parse a string (LookIn), and return matches to a
    ' pattern (PatternStr).  Use Pos to indicate which match you want:
    ' Pos omitted               : function returns a zero-based array of all matches
    ' Pos = 0                   : the last match
    ' Pos = 1                   : the first match
    ' Pos = 2                   : the second match
    ' Pos = <positive integer>  : the Nth match
    ' If Pos is greater than the number of matches, is negative, or is non-numeric, the function
    ' returns an empty string.  If no match is found, the function returns an empty string
    
    ' If MatchCase is omitted or True (default for RegExp) then the Pattern must match case (and
    ' thus you may have to use [a-zA-Z] instead of just [a-z] or [A-Z]).
    
    ' If you use this function in Excel, you can use range references for any of the arguments.
    ' If you use this in Excel and return the full array, make sure to set up the formula as an
    ' array formula.  If you need the array formula to go down a column, use TRANSPOSE()
    
    Static RegX As Object
    Dim TheMatches As Object
    Dim Answer() As String
    Dim Counter As Long
    
    ' Evaluate Pos.  If it is there, it must be numeric and converted to Long
    If Not IsMissing(Pos) Then
        If Not IsNumeric(Pos) Then
            RegExpFind = ""
            Exit Function
        Else
            Pos = CLng(Pos)
        End If
    End If
    
    ' Create instance of RegExp object
    If RegX Is Nothing Then Set RegX = CreateObject("VBScript.RegExp")
    With RegX
        .Pattern = PatternStr
        .Global = True
        .IgnoreCase = Not MatchCase
    End With
        
    ' Test to see if there are any matches
    If RegX.test(LookIn) Then
        
        ' Run RegExp to get the matches, which are returned as a zero-based collection
        Set TheMatches = RegX.Execute(LookIn)
        
        ' If Pos is missing, user wants array of all matches.  Build it and assign it as the
        ' function's return value
        If IsMissing(Pos) Then
            ReDim Answer(0 To TheMatches.Count - 1) As String
            For Counter = 0 To UBound(Answer)
                Answer(Counter) = TheMatches(Counter)
            Next
            RegExpFind = Answer
        
        ' User wanted the Nth match (or last match, if Pos = 0).  Get the Nth value, if possible
        Else
            Select Case Pos
                Case 0                          ' Last match
                    RegExpFind = TheMatches(TheMatches.Count - 1)
                Case 1 To TheMatches.Count      ' Nth match
                    RegExpFind = TheMatches(Pos - 1)
                Case Else                       ' Invalid item number
                    RegExpFind = ""
            End Select
        End If
    
    ' If there are no matches, return empty string
    Else
        RegExpFind = ""
    End If
    
    ' Release object variables
    Set TheMatches = Nothing
    
End Function

Open in new window

0
 
LVL 11

Accepted Solution

by:
Missus Miss_Sellaneus earned 500 total points
ID: 24135408
sumhungl0,
Is it middle initial or middle name?

The following query works if it's middle initial.  I can change to work with a whole middle name, if needed.

SELECT names.pName, 
UCASE(left(pname,1)) & LCASE(MID(Left(pname,InStr(1,pname,' ',1)),2)) AS lname,
 
UCASE(LEFT(IIf(InStr(1,Trim(Mid(pname,InStr(1,pname,' ',1))),' ',1)>0,
Trim(Left(LTrim(Mid(pname,InStr(1,pname,' ',1))),InStr(1,LTrim(Mid(pname,InStr(1,pname,' ',1))),' ',1))),
TRIM(LTrim(Mid(pname,InStr(1,pname,' ',1))))),1)) &
LCASE(MID(IIf(InStr(1,Trim(Mid(pname,InStr(1,pname,' ',1))),' ',1)>0,
Trim(Left(LTrim(Mid(pname,InStr(1,pname,' ',1))),InStr(1,LTrim(Mid(pname,InStr(1,pname,' ',1))),' ',1))),
TRIM(LTrim(Mid(pname,InStr(1,pname,' ',1))))),2)) AS fname,
 
IIF(LEFT(RIGHT(TRIM(pname),2),1) = ' ', UCASE(RIGHT(pname,1)), '') AS mi
 
FROM [names];

Open in new window

0
 

Author Closing Comment

by:sumhungl0
ID: 31569536
sorry it took so long, thank you so much.  worked great.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question