Technical Article

Parse list of names from Outlook

,

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 @names			VARCHAR(4000),
	@index			INT,
	@lt_index		INT,
	@gt_index		INT,
	@comma_index		INT,
	@full_name		VARCHAR(50),
	@first_name		VARCHAR(20),
	@last_name		VARCHAR(30),
	@last_name_first	BIT,
	@remove_mi		BIT

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

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (3)

You rated this post out of 5. Change rating

Share

Tags

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4 (3)

You rated this post out of 5. Change rating