0

I am still scratching my head on how to extract the lastname,firstname, suffix using only SELECT. Stupid vendor has everything in 1 field instead of doing 1NF. If someone could at least help extracting the last name and first name (suffix is optional but will help) that will be very helpful. Some name examples below:

Currently names in database
Name
1) Hoover,James
2) Hoover JR,James
3) Hoover,James B
4) Hoover JR,James B
5) Hoover II,James B

Desired result
       LastName     FirstName   Suffix
    1) Hoover       James
    2) Hoover       James       JR
    3) Hoover       James
    4) Hoover       James       JR
    5) Hoover II    James

Even if I can get at least LastName (Hoover) and FirstName(James) that will be ok. Need to be done all in SELECT statement.

sky_limit
  • 133
  • 2
  • 11
  • Did not the answer you accepted on your [previous question](http://stackoverflow.com/questions/32363847/extract-last-name-first-name-and-suffix-into-separate-columns) solve your problem? – jpw Sep 04 '15 at 00:02
  • I found out that I need to do everything in the SELECT statement and format of the names are different. So instead of continuing the previous thread, I thought to open a new one since the other solution may help others. – sky_limit Sep 04 '15 at 02:11

1 Answers1

0

You can use SUBSTRING and CHARINDEX function to get what you want.

the code is something like this.

To get the Surname: SELECT SUBSTRING(Name,0,LEN(LEFT(Name,CHARINDEX(',', Name))))

To get the Firstname: SELECT SUBSTRING(Name, LEN(LEFT(Name,CHARINDEX(',', Name))) + 1, LEN(Name))

You can refer to this link: Get everything after and before certain character in SQL Server

Regards

Community
  • 1
  • 1
japzdivino
  • 1,736
  • 3
  • 17
  • 25