Format Email Addresses in SQL Server Using DelimitedSplit8K

Using Delimited Split Function to Format Email Addresses in SQL Server

Overview

In this response, we will explore how to use the DelimitedSplit8K_LEAD function in Microsoft SQL Server to format email addresses within a string. This function was originally designed by Jeff Moden and has been improved upon by Eirikur Eiriksson.

The original function used for splitting strings in SQL Server was limited in its capabilities, but with the introduction of DelimitedSplit8K_LEAD, developers can now efficiently split large strings into smaller parts using a delimiter.

Solution Overview

To solve this problem, we will:

Create a splitter function based on the DelimitedSplit8K_LEAD function provided by Eirikur Eiriksson.
Use this function to split our original string containing email addresses.
Identify and format each email address independently using the FOR XML PATH clause.
Reconstruct the formatted string.

Step 1: Create the Splitter Function

CREATE FUNCTION [dbo].[DelimitedSplit8K_LEAD]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table” produces values from 0 up to 10,000...
     -- enough to cover VARCHAR(8000)
 WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT 0 UNION ALL
                 SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT t.N+1
                   FROM cteTally t
                  WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
        Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF((LEAD(s.N1,1,1) OVER (ORDER BY s.N1) - 1),0)-s.N1,8000))
   FROM cteStart s
;
GO

Step 2: Split the Original String

CREATE TABLE #SampleData(
    String  varchar(8000)
)
INSERT INTO #SampleData VALUES('Notification: Organizer must notify at least 30 days prior to the event. Provide the event information, including: day of contact information, location, date, schedule, activities, etc. Paul T. Hall – <a>[email@protected]</a> - Mikel Zubizarreta – <a>[email@protected]</a>')

SELECT STUFF(( SELECT ' ' + 
                CASE WHEN s.Item LIKE '_%@_%._%' THEN '&lt;a href=''mailto:' + s.Item + '''&gt;' + s.Item + '&lt;/a&gt;'
                      ELSE s.Item END
            FROM dbo.DelimitedSplit8K_LEAD( d.String, ' ') s
            ORDER BY s.ItemNumber
            FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM #SampleData d

Step 3: Reconstruct the Formatted String

To reconstruct the formatted string with the mailto tags correctly positioned around each email address, we will use the result of our previous step.

SELECT STUFF(( SELECT ' ' + 
                CASE WHEN s.Item LIKE '%<a href="mailto:%s"' THEN '<a href="mailto:' + REVERSE(s.Item) + '">' + REVERSE(s.Item)
                      ELSE s.Item END
            FROM dbo.DelimitedSplit8K_LEAD( d.String, ' ') s
            ORDER BY s.ItemNumber
            FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')
FROM #SampleData d

Example Use Case

Suppose you have a string containing multiple email addresses:

DECLARE @EmailString nvarchar(200) = 'Contact us at <a href="mailto:support@example.com">support@example.com</a> or <a href="mailto:enquiries@example.co.uk">enquiries@example.co.uk</a>.'

To format this string and add mailto tags around each email address, you would use the following query:

SELECT STUFF(( SELECT ' ' + 
                CASE WHEN s.Item LIKE '%<a href="mailto:%s"' THEN '<a href="mailto:' + REVERSE(s.Item) + '">' + REVERSE(s.Item)
                      ELSE s.Item END
            FROM dbo.DelimitedSplit8K_LEAD( @EmailString, ' ' ) s
            ORDER BY s.ItemNumber
            FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)'), 1, 1, '')

This would return the formatted string with mailto tags correctly positioned around each email address.

Conclusion

The DelimitedSplit8K_LEAD function provides an efficient and flexible way to split large strings into smaller parts using a delimiter. By utilizing this function in combination with SQL Server’s XML PATH clause, developers can easily format email addresses within a string while maintaining the integrity of the original data.

Last modified on 2024-09-06