Parse list of names from Outlook

Noel Scheaffer, 2016-03-23 (first published: 2015-07-20)

This script's target audience is likely pretty small, but I have used it quite a bit over the years so I thought I would share it anyway.

When I am writing an email or document in which I need to include a list of names I want to make sure I spell them correctly. So I start a new email and add each person I want as a recipient. I then copy that semi-colon separated list of names from the Outlook To box and set the @names variable in that string.

My script will parse the string removing the email addresses, the semi-colons and the commas and reverse that last name and first name (if @last_name_first = 0) and return just a list of names as "FirstName LastName". If you want the names in "LastName, FirstName" format just set @last_name_first = 1.

If you have middle initials they can be excluded by setting @remove_mi = 1. If you have a name like "O'Brien", you will need to manually change it to "O''Brien" (that's two single quotes between the "O" and the "B").

My example would yield...

Bob O'Brien

Jane X. Doe

Enjoy,

Noel

DECLARE @namesVARCHAR(4000), @indexINT, @lt_indexINT, @gt_indexINT, @comma_indexINT, @full_nameVARCHAR(50), @first_nameVARCHAR(20), @last_nameVARCHAR(30), @last_name_firstBIT, @remove_miBIT SET @names = 'O''Brien, Bob <bobrien@somedomain.com>; Doe, Jane X. <jxdoe@somedomain.com>' SET @last_name_first = 0 SET @remove_mi = 0 SET @index = 1 WHILE @index < LEN(@names) BEGIN SET @lt_index = CHARINDEX('<', @names, @index) SET @gt_index = CHARINDEX('>', @names, @index) SET @full_name = SUBSTRING(@names, @index, (@lt_index - 1) - @index) SET @comma_index = CHARINDEX(',', @full_name) IF @comma_index = 0 PRINT @full_name ELSE BEGIN SET @first_name = SUBSTRING(@full_name, @comma_index + 2, LEN(@full_name) - @comma_index) SET @last_name = SUBSTRING(@full_name, 1, @comma_index - 1) IF (@remove_mi = 1) AND (CHARINDEX('.', @first_name, 1) > 0) SET @first_name = LEFT(@first_name, LEN(@first_name) - 3) IF @last_name_first = 1 PRINT @last_name + ', ' + @first_name ELSE PRINT @first_name + ' ' + @last_name END SET @index = @gt_index + 3 END

Parse list of names from Outlook

Rate

Share

Share

Rate