July 22, 2015 at 10:54 am
Comments posted to this topic are about the item Parse list of names from Outlook
August 13, 2015 at 1:27 am
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
August 16, 2015 at 10:22 am
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
August 18, 2015 at 12:48 am
Hi trimjib
That looks much better and a lot easier to maintain, thanks!:-)
August 18, 2015 at 8:01 am
Thanks for the script.
March 24, 2016 at 6:49 am
Thanks for the script.
March 28, 2020 at 1:33 pm
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
March 28, 2020 at 2:33 pm
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
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy