Parse list of names from Outlook

Question

Post reply

Parse list of names from Outlook

nscheaffer

SSC Enthusiast

Points: 133
More actions
July 22, 2015 at 10:54 am

#398784

Comments posted to this topic are about the item Parse list of names from Outlook

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

hannes.malan SSC Veteran Points: 286 More actions · Answer 1

Hi Noel

Thanks for sharing your script. It reminded me of a time I needed to do something similar.

I have taken the liberty of modifying your code. If I add or remove spaces after the semi-colon the output gets broken.

Now I split the list into a table and update the table with the names( as well as the emails).

🙂

declare

@namesvarchar(4000),@indexint

,@lt_indexint--,@gt_indexint

,@comma_indexint,@full_namevarchar(50)

,@first_namevarchar(20),@last_namevarchar(30)

,@last_name_firstbit

select @last_name_first = 0

SET @names = 'O''Brien, Bob <bobrien@somedomain.com>;Doe, Jane <jdoe@somedomain.com> ; Van der Merwe, Koos <bla@bla>'

-----------------------Split string into table

declare @List table(Row int,sValue varchar(200),Name varchar(100),Email varchar(100))

declare @string AS VARCHAR(4000), @Seperator AS CHAR(1)

select @Seperator = ';', @string = @names

insert into @List

select

row_number() over (order by number) as 'Row'

,ltrim(rtrim(substring(@string, number, charindex(@Seperator, @string + @Seperator, number) - number))) AS sValue

,'',''

from (select number from master..spt_values where type='p') as numbers

where substring(@Seperator + @string, number, 1) = @Seperator

order by number

-----------------------Update table row by row :P

declare @Row int,@sValue varchar(200)

declare cur cursor for select Row,sValue from @List

open cur

fetch next from cur into @Row, @sValue

while @@fetch_status = 0 begin

select @index = 1

select @lt_index = charindex('<', @sValue, @index)

--select @gt_index = charindex('>', @sValue, @index)

select @full_name = substring(@sValue, @index, (@lt_index - 1) - @index)

select @comma_index = charindex(',', @full_name)

-----update the names

IF @comma_index = 0

update @List set Name = @full_name where Row = @Row

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 @last_name_first = 1

update @List set Name = @last_name + ', ' + @first_name where Row = @Row

else

update @List set Name = @first_name + ' ' + @last_name where Row = @Row

end

-----update the emails

update @List set Email = substring(@sValue, @lt_index + 1, len(@sValue) - (@lt_index+1)) where Row = @Row

fetch next from cur into @Row, @sValue

end

close cur

deallocate cur

select * from @List

----------------------

One can then use it to update a database perhaps..

Best Regards

Hannes

trimjib SSC Veteran Points: 234 More actions · Answer 2

Noel,

Gotta thank you for your notion of using OutLook to validate and standardize. Really good.

For simple string parsing I've been experimenting with CROSS APPLY. I like it because, once I got used to it, it seems simpler to understand and document.

declare @names nvarchar(max)

SET @names = N'O''Brien, Bob <bobrien@somedomain.com>;Doe, Jane <jdoe@somedomain.com> ; Van der Merwe, Koos <bla@bla>'

select

ltrim(rtrim(s3.first)) FirstName

,ltrim(rtrim(s3.last)) LastName

,ltrim(rtrim(s2.email)) Email

from dbo.DelimitedSplit8K(@names, N';') s1 -- convert to rows (By Jeff Moden)

cross apply (select charindex(N'<', s1.Item ) ) Idxs(n) -- locate email phrase

cross apply (select substring( s1.Item, 1, Idxs.n - 1 ) as names, substring(s1.item, idxs.n+1, len(s1.item) - idxs.n - 1 ) email) s2 -- get names, email

cross apply (select charIndex(N',', s2.names ) ) Idxc(n) -- locate name splitter

cross apply (select substring( s2.names, 1, idxc.n -1 ) last, substring( s2.names, idxc.n + 1 , len(s2.names) - idxc.n ) first) s3 -- get first, last

hannes.malan SSC Veteran Points: 286 More actions · Answer 3

Hi trimjib

That looks much better and a lot easier to maintain, thanks!:-)

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

August 18, 2015 at 8:01 am

#1820744

Thanks for the script.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

March 24, 2016 at 6:49 am

#1867395

Thanks for the script.

nscheaffer SSC Enthusiast Points: 133 More actions · Answer 6

Since the arrival of STRING_SPLIT() in SQL Server 2016 we can use that in place of DelimitedSplit8K() function.

DECLARE @names VARCHAR(4000)

SET @names = N'O''Brien, Bob <bobrien@somedomain.com>; Doe, Jane <jdoe@somedomain.com>; Van der Merwe, Koos <bla@bla>';

SELECT
LTRIM(RTRIM(s3.first)) FirstName
, LTRIM(RTRIM(s3.last)) LastName
, LTRIM(RTRIM(s2.email)) Email
FROM STRING_SPLIT(@names, N';') s1 -- convert to rows (By Jeff Moden)

CROSS APPLY (SELECT CHARINDEX(N'<', s1.value)) Idxs(n) -- locate email phrase

CROSS APPLY (
SELECT
SUBSTRING(s1.value, 1, Idxs.n - 1) AS names
, SUBSTRING(s1.value, Idxs.n + 1, LEN(s1.value) - Idxs.n - 1) email
) s2 -- get names, email

CROSS APPLY (SELECT CHARINDEX(N',', s2.names)) Idxc(n) -- locate name splitter

CROSS APPLY (
SELECT
SUBSTRING(s2.names, 1, Idxc.n - 1) last
, SUBSTRING(s2.names, Idxc.n + 1, LEN(s2.names) - Idxc.n) first
) s3; -- get first, last

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 7

I agree. Because there is no requirement for maintaining the order in which the parsed elements appear, String_Split() is the better choice here.

The thing is that you have to be careful to not limit yourself. I have seen distribution and CC lists that exceed even VARCHAR(8000), never mind VARCHAR(4000).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)