SDU Tools: Extracting initials from a name in SQL Server T-SQL

I recently came across a requirement to extract someone’s initials from within their name. That was a new one for me, so we added a new function into our free SDU Tools for developers and DBAs. It’s called InitialsFromName.

It’s a straightforward scalar function that takes two parameters:

@Name nvarchar(max) is the name to extract the initials from

@Separator nvarchar(max) is a separator placed between the returned initials.  (Make it an empty string for none)

In the main image above, you can see the difference between the first two examples. The only difference is the separator.

You can use our tools as a complete set or as a library that provides great examples of how to write functions like these.

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE FUNCTION [SDU_Tools].[InitialsFromName]
(
    @Name nvarchar(max),
    @Separator nvarchar(max)
)
RETURNS nvarchar(max)
AS
-- Function:      Returns the initials from a name
-- Parameters:    @Name nvarchar(max) - the string to process
-- @Separator nvarchar(max) - the separator between initials
-- Action:        Returns the initials from a name, separated by a separator
-- Return:        Single string
-- Refer to this video: https://youtu.be/bWPsidPGrCQ
--
-- Test examples: 
/*

SELECT SDU_Tools.InitialsFromName(N'Mary Johanssen', N'');
SELECT SDU_Tools.InitialsFromName(N'Mary Johanssen', N' ');
SELECT SDU_Tools.InitialsFromName(N'Thomas', N' ');
SELECT SDU_Tools.InitialsFromName(N'Test Test', NULL);

*/
BEGIN
    DECLARE @Response nvarchar(max) = N'';
    DECLARE @StringToProcess nvarchar(max);
    DECLARE @CharacterCounter int = 0;
    DECLARE @Character nchar(1);
    DECLARE @InAWord bit;
    
    SET @StringToProcess = UPPER(LTRIM(RTRIM(@Name)));
    SET @InAWord = 0;
    
    WHILE @CharacterCounter < LEN(@StringToProcess)
    BEGIN
        SET @CharacterCounter += 1;
        SET @Character = SUBSTRING(@StringToProcess, @CharacterCounter, 1);
        IF @Character IN (N' ', NCHAR(9))
        BEGIN
            IF @InAWord <> 0
            BEGIN
                SET @InAWord = 0;
            END;
        END ELSE BEGIN -- not whitespace
            IF @InAWord = 0 -- start of a word
            BEGIN
                SET @InAWord = 1;
                IF DATALENGTH(@Response) > 0
                BEGIN
                    SET @Response += @Separator;
                END;
                SET @Response += @Character;
            END;
        END;
    END;
    
    RETURN @Response;
END;

2020-05-06