Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

need update query to split name field into first last mi

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
sumhungl0
Asked:
sumhungl0
  • 8
  • 6
  • 3
  • +1
1 Solution
 
YZlatCommented:
If they are separate dby spaces, something like this should work:

SELECT SubString(Name, 1, CharIndex(' ', Name)-1)
0
 
sumhungl0Author Commented:
does SubString go into criteria?
0
 
YZlatCommented:
what do you mean?


Also can you gvie me a sample string format for your field?  Ex: John Doe, M
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sumhungl0Author Commented:
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
 
sumhungl0Author Commented:
the above sql gives me an error.
Undefined function 'SubString' in expression.
0
 
Missus Miss_SellaneusCommented:
Is there a comma after the last name in pName?
0
 
sumhungl0Author Commented:
no, its Last SPACE First SPACE Mi
0
 
Missus Miss_SellaneusCommented:
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
 
sumhungl0Author Commented:
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
 
Missus Miss_SellaneusCommented:
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
 
Missus Miss_SellaneusCommented:
Sorry, that capitalizes whole word. Use UCASE(LEFT(word,1)) & LCASE(MID(word,2)) to cap first letter only.
0
 
sumhungl0Author Commented:
can you put that into your example, i dont know where to put ucase and lcase?
0
 
YZlatCommented:
oops, Substring is for SQL Server, I didn't notice your was Access db
0
 
Missus Miss_SellaneusCommented:
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
 
sumhungl0Author Commented:
yea ive got this all messed up, ill wait till you get time.  thanks miss
0
 
Patrick MatthewsCommented:
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
 
Missus Miss_SellaneusCommented:
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
 
sumhungl0Author Commented:
sorry it took so long, thank you so much.  worked great.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 8
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now