Get initials of names

Posted on 2013-09-11
Medium Priority
Last Modified: 2013-09-12
I have thousands of names in a Player Name column, and I'm wanting to store within another column the initials.

For example the following names would convert to the names on the right:

John McCarthy Blackham > JM Blackham
Nathaniel Frampton Davis Thomson > NFD Thomson
Henry Jupp -> H Jupp

How could this be done, so I could run something like this?

Update Player
Set PlayerNameInitials = getinitials()

Open in new window

Question by:PeterErhard
  • 3

Expert Comment

ID: 39482822
You could create a stored function getinitials which has an input varchar parameter (the full name) and returns your desired result.
The stored function can use a repeat... until loop.

Check this example: http://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql
Here is the MySQL documentation for Create Function and Repeat syntax.
LVL 27

Expert Comment

by:Cornelia Yoder
ID: 39482832
I assume the delimiters are always a space?

In MySQL, you could use SUBSTRING_INDEX() to find the last space and SUBSTRING() to pick out the last name, then repeat that on the earlier words, but I think you'd need a CASE() to handle 1, 2, 3, or 4 part names (could there be 5?).  SUBSTRING() can then give you the first letter of each word.

Do you have to do this in MySQL query?  If it's a one time thing, it would be much easier to do it in php.


$player = "Nathaniel Frampton Davis Thomson";
$array = explode(" ",$player);
for ($i=0;$i<count($array)-1;$i++)
  $newname .= substr($array[$i],0,1);
$newname .= " ".$array[count($array)-1];

echo $newname;

Accepted Solution

Argenti earned 2000 total points
ID: 39482872
Here you have the stored function code:
USE `my_own_database`;
DROP function IF EXISTS `get_shortened_name`;

USE `test`$$
CREATE FUNCTION `get_shortened_name`(full_name varchar(80)) RETURNS varchar(50) 
	declare nPos int;
	declare sWord varchar(25);
	declare sResult varchar(50); 
	declare sTemp varchar(80);
	set nPos = 0;
    set sTemp = trim(full_name);
	set sResult = '';

while locate(' ', sTemp) > 0 do
	set nPos = locate(' ', sTemp); -- find the position of ' '
	set sResult = concat(sResult, upper(left(sTemp, 1)));
	set sTemp = right(sTemp, length(sTemp) - nPos);	-- remaining part
end while;

	set sResult = concat(sResult, ' ', sTemp);

RETURN sResult;


Open in new window

then call it in your query:
SELECT FullName, get_shortened_name(FullName) 
FROM NamesTable;

Open in new window


Author Closing Comment

ID: 39486266
Awesome function, thanks so much. That works an absolute treat :)

Expert Comment

ID: 39486311

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

624 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