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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
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; |