Parse list of names from Outlook

  • nscheaffer

    SSC Enthusiast

    Points: 131

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

  • hannes.malan

    SSC Veteran

    Points: 253

    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

    @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

    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 😛

    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: 225

    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: 253

    Hi trimjib

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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 6 posts - 1 through 6 (of 6 total)

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