Sometimes I come across situations where I need to extract the individual parts of a full name from a database record that relates to a person e.g. a customer or an employee.
This may be part of general application logic or perhaps something that needs to be carried out during a database migration.
For example, perhaps the initial software requirements were met by a simple full name or account name field, but now the individual parts of the name need to be stored separately to make life easier when mail merging or producing marketing material.
To achieve this we can turn to SQL to split up a full name into its constituent parts.
SQL Functions
There are a few SQL string functions we can avail of to get the job done, as follows.
LEN
Returns the length of the specified string. Requires a single expression argument.
e.g. SELECT LEN('Jonathan Crozier')
Result: 16
CHARINDEX
Returns the index of the first occurrence of a specified character. Requires search_expression, expression_to_be_searched and start_location arguments.
e.g. SELECT CHARINDEX('C', 'Jonathan Crozier', 0)
Result: 10
SUBSTRING
Returns part of a string based on a start index and length parameter. Requires expression, starting_position and length arguments.
e.g. SELECT SUBSTRING('Jonathan Crozier', 10, 7)
Result: Crozier
LTRIM
Removes whitespace from the left side of the specified string. Requires a single expression argument.
e.g. SELECT LTRIM(' Jonathan Crozier')
Result: Jonathan Crozier
i.e. the 3 space characters at the start of the string have been removed.
RTRIM
Removes whitespace from the right side of the specified string. Requires a single expression argument.
e.g. SELECT RTRIM('Jonathan Crozier ')
Result: Jonathan Crozier
i.e. the 3 space characters at the end of the string have been removed.
By using a combination of the above SQL functions in a script we can figure out how to split up a full name into Title, First Name and Last Name fields with a fairly high degree of accuracy.
The Script
The SQL for achieving a name split into the aforementioned fields is included below.
-- Parse the individual name parts from the full name of each customer record. -- Update customer records for which a Title, First Name and Last Name have not already been set. SET NOCOUNT ON GO -- Customer table column values. DECLARE @CustomerId INT; DECLARE @CustomerAccountName NVARCHAR(108); DECLARE @CustomerTitle NVARCHAR(8); DECLARE @CustomerFirstName NVARCHAR(50); DECLARE @CustomerLastName NVARCHAR(50); -- Initialise cursor. DECLARE CustomersCursor CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR SELECT DISTINCT CustomerKey, AccountName, Title, FirstName, LastName FROM DimCustomer OPEN CustomersCursor FETCH NEXT FROM CustomersCursor INTO @CustomerId, @CustomerAccountName, @CustomerTitle, @CustomerFirstName, @CustomerLastName WHILE @@FETCH_STATUS = 0 BEGIN -- Temporary variables. DECLARE @Title NVARCHAR(8); DECLARE @FirstName NVARCHAR(50); DECLARE @LastName NVARCHAR(50); -- Index and word variables. DECLARE @AccountNameEndIndex INT; DECLARE @FirstWordEndIndex INT; DECLARE @SecondWordEndIndex INT; DECLARE @ThirdWordEndIndex INT; DECLARE @FirstWord NVARCHAR(8); DECLARE @SecondWord NVARCHAR(50); DECLARE @ThirdWord NVARCHAR(50); -- Get the last index. SET @AccountNameEndIndex = LEN(@CustomerAccountName) - 1; -- Get the first word. SET @FirstWordEndIndex = (SELECT CHARINDEX(' ', @CustomerAccountName, 0)); SET @FirstWord = SUBSTRING(@CustomerAccountName, 0, @FirstWordEndIndex); -- Get the second word. SET @SecondWordEndIndex = (SELECT CHARINDEX(' ', @CustomerAccountName, @FirstWordEndIndex + 1)); IF @SecondWordEndIndex = 0 BEGIN SELECT @SecondWordEndIndex = @AccountNameEndIndex; SET @SecondWord = SUBSTRING(@CustomerAccountName, @FirstWordEndIndex + 1, @SecondWordEndIndex - (@FirstWordEndIndex - 1)); END ELSE BEGIN SET @SecondWord = SUBSTRING(@CustomerAccountName, @FirstWordEndIndex + 1, @SecondWordEndIndex - (@FirstWordEndIndex + 1)); END -- Get the third word. SET @ThirdWordEndIndex = @AccountNameEndIndex; IF @ThirdWordEndIndex <> @SecondWordEndIndex BEGIN SET @ThirdWord = SUBSTRING(@CustomerAccountName, @SecondWordEndIndex + 1, @ThirdWordEndIndex - (@SecondWordEndIndex - 1)); END -- Check if the first word is a 'Title'. SELECT @Title = CASE LTRIM(RTRIM(@FirstWord)) WHEN 'Dr' THEN 'Dr' WHEN 'Dr.' THEN 'Dr.' WHEN 'Miss' THEN 'Miss' WHEN 'Miss.' THEN 'Miss.' WHEN 'Mr' THEN 'Mr' WHEN 'Mr.' THEN 'Mr.' WHEN 'Mrs' THEN 'Mrs' WHEN 'Mrs.' THEN 'Mrs.' WHEN 'Ms' THEN 'Ms' WHEN 'Ms.' THEN 'Ms.' WHEN 'Rev' THEN 'Rev' WHEN 'Rev.' THEN 'Rev.' ELSE '' END IF @Title = '' BEGIN IF LTRIM(RTRIM(@FirstWord)) = '' BEGIN -- There is only one word, use this as the First Name. SELECT @FirstName = LTRIM(RTRIM(@SecondWord)); SELECT @LastName = ''; END ELSE BEGIN -- There are two words, set First Name and Last Name. SELECT @FirstName = LTRIM(RTRIM(@FirstWord)); SELECT @LastName = LTRIM(RTRIM(@SecondWord)); END END ELSE BEGIN -- There are three words, set First Name and Last Name. -- Title has already been set. SELECT @FirstName = LTRIM(RTRIM(@SecondWord)); SELECT @LastName = LTRIM(RTRIM(@ThirdWord)); END -- Output the combination of the name parts for reference. PRINT @Title + ' ' + @FirstName + ' ' + @LastName; -- Update the current Customer values. IF (LEN(ISNULL(@CustomerTitle , '')) = 0) AND (LEN(ISNULL(@CustomerFirstName, '')) = 0) AND (LEN(ISNULL(@CustomerLastName , '')) = 0) BEGIN UPDATE DimCustomer SET Title = @Title, FirstName = @FirstName, LastName = @LastName WHERE CustomerKey = @CustomerId; END FETCH NEXT FROM CustomersCursor INTO @CustomerId, @CustomerAccountName, @CustomerTitle, @CustomerFirstName, @CustomerLastName END CLOSE CustomersCursor DEALLOCATE CustomersCursor
Note that you must adjust the script to suit your database schema before running it. Always back up your database before running a script for the first time and always test it on a non-production database first.
The SQL uses a CURSOR to temporarily store and iterate through the customer set. While iterating through the results the first, second and third words are parsed from the full name. The first word is then checked to see if it is a title. The individual name part variables are then set and the customer record is updated if required.
Note that the title check, of course, isn’t a perfect solution and depends on the titles being input in a consistent manner.
Script Results
Before running the script, I carried out some reverse engineering in the standard AdventureWorks database.
I added an ‘AccountName’ column and populated it based on a combination of the ‘Title’, ‘FirstName’ and ‘LastName’ columns.
After running the script and executing a select query against the ‘DimCustomer’ table, the sample output looks like the screenshot below.
As you can see, the script has accurately parsed the individual name parts from the ‘AccountName’ column.
In cases where there are more than three words to split, the additional words will be stored in the ‘LastName’ column.
Adapting the Script
It’s always useful to have a script like this available when the need for it arises.
Aside from column types and lengths, the initial SELECT and closing UPDATE statements are the main parts of the script that need to be changed on a case by case basis.
The script could also be extended to accommodate slightly more complex requirements.
For example, if a ‘MiddleNames’ column needed to be populated. In this case, the logic could be reworked to store any additional name parts in the ‘MiddleNames’ column instead of the ‘LastName’ column which would be reserved for the last word.
The script from this article is part of my SQL Scripts GitHub Repository where you can find other useful SQL scripts which will help you to solve other problems and save time!
Comments