-1

I have a column of First Names, however, both first name and middle name are combined in this column. For ex the column name is FirstName and contains Jim JR. next to this column I have a MiddleName column, which is currently blank. I want to split the JR. after Jim from the FirstName column and into the MiddleName.

swe
  • 1,416
  • 16
  • 26
Kevin M
  • 367
  • 2
  • 4
  • 7
  • 1
    Possible duplicate of [T-SQL substring - separating first and last name](http://stackoverflow.com/questions/10921400/t-sql-substring-separating-first-and-last-name) – S3S Sep 26 '16 at 18:36
  • please have a look at [how-to-ask](http://stackoverflow.com/help/how-to-ask) – swe Sep 26 '16 at 18:42
  • 1
    From a design perspective, middle name columns are problematic. For instance, a girl could be name Jo Ann Michelle Smith, where "Jo Ann" is her first name, and Michelle her middle name. It's even worse with cultural naming systems where there isn't even a concept of middle names, but multi-word first names, like Abdul Rahman, where Rahman is the most important part of the first name, and is NOT a middle name. If your database has a given names column and a family names column, don't try to convert that to first/middle/last, you'll be going from better to worse. – hatchet - done with SOverflow Sep 26 '16 at 18:48

1 Answers1

0

If they are separated by space, you can use the CHARINDEX(' ',FirstName) and SUBSTRING(). like:

SUBSTRING(FirstName, 
          CHARINDEX(' ',FirstName),
          LEN(FirstName)-CHARINDEX(' ',FirstName))
S3S
  • 24,809
  • 5
  • 26
  • 45
Rafael Roland Passarella
  • 3
  • 4