Monday, August 21, 2017

Get first name and initial of last name

Sometimes names are longer than convenient for displaying in tables and graphs, so you may consider making them shorter.

Considering the name "Jonathan White" as example, here are a few tips for you:

Only first name

SELECT LEFT(Name, CHARINDEX(' ', Name) - 1)
"Jonathan"

First name plus initial of last name

SELECT LEFT(Name, CHARINDEX(' ', Name) + 1)
"Jonathan W"

Just initials

SELECT  SUBSTRING(Name, 1, 1) + SUBSTRING(Name, CHARINDEX(' ', Name) + 1, 1)
"JW"

Notes

There are some caveats with the above suggestions. They may not work as expected if:
  • If there is only one name, e.g. "Peter"
  • If there is more than 2 names, e.g. "Jennifer Alexandra Smith"
For more advanced processing a UDF (function) or SP (procedure) is probably better adviced.