CODEWITHSUNDEEP
sql-servert-sqlazure-sql-databaseazure-sql-server
user7415753
user7415753

Reputation: 198

T-SQL Search and replace pattern

My brain just isn't working today. I have a string that has a comma separated list of email addresses similar to below

removethis<[email protected]>; andthis<[email protected]>; [email protected]; butthis<[email protected]>

I would like to just return a semicolon separated list of email address so the output I am looking for would be something like this

[email protected];[email protected];[email protected];[email protected]

I would like to achieve this in a T-SQL function so I can reuse this if possible.

The list that gets passed as seen in example above can either be just an email address or a name followed by email between. The logic needs to check if it's just an email then return it, if it's a name and an email (indicated by pattern name < email > then just return what's between the <>

for info: SQL version: Azure SQL Database

Example scenario:

DECLARE @X varchar(max) = '
removethis<[email protected]>;andthis<[email protected]>;[email protected];butthis<[email protected]>'
Select @udfFormatEmail(@X)

Expected output:

[email protected];[email protected];[email protected];[email protected]

Upvotes: 0

Views: 101

Answers (3)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

Please try the following solution.

It leverages advanced functionality of the TRIM() function that became available staring from SQL Server 2017 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, emailList VARCHAR(MAX));  
INSERT INTO @tbl (emailList) VALUES
('removethis<[email protected]>; andthis<[email protected]>; [email protected]; butthis<[email protected]>');
-- DDL and sample data population, end

SELECT a.emailList
    , STRING_AGG(TRIM('<> ' FROM RIGHT(value, LEN(value) - pos)), ';') AS cleansedEmailList
FROM @tbl AS a
    CROSS APPLY STRING_SPLIT(emailList, ';')
    CROSS APPLY (SELECT CHARINDEX('<', value)) AS t(pos)
GROUP BY a.emailList;

Output

+------------------------------------------------------------------------------------------------+---------------------------------------------------------------+
|                                           emailList                                            |                       cleansedEmailList                       |
+------------------------------------------------------------------------------------------------+---------------------------------------------------------------+
| removethis<[email protected]>; andthis<[email protected]>; [email protected]; butthis<[email protected]> | [email protected];[email protected];[email protected];[email protected] |
+------------------------------------------------------------------------------------------------+---------------------------------------------------------------+

Upvotes: 0

Stu
Stu

Reputation: 32609

You could do this using a combination of string_split and string_agg and stuff to remove all the characters before each '<'.

declare @X varchar(max) ='removethis<[email protected]>;andthis<[email protected]>;[email protected];butthis<[email protected]>'

select String_Agg(Replace(Stuff(value,1, CharIndex('<',value),''),'>',''),';')
from String_Split(@X,';')

Upvotes: 2

Charlieface
Charlieface

Reputation: 71578

You really should not store multiple pieces of info in the same column, this applies both to multiple emails, and to the email name and value.

Be that as it may, you can do this in a number of steps:

  • Split the string by ;
  • Trim spaces
  • Replace the > with nothing
  • Find the position of < if any
  • Take the substring starting the character after that, or the whole string if none
  • Aggregate it back up
SELECT STRING_AGG(
    ISNULL(
        SUBSTRING(v1.email, v2.leftArrow + 1, LEN(v1.email)),
        v1.email
   ), ';')
FROM YourTable t
CROSS APPLY STRING_SPLIT(t.emails, ';') s
CROSS APPLY (VALUES (REPLACE(TRIM(s.value), '>', '')     )) v1(email)
CROSS APPLY (VALUES (NULLIF(CHARINDEX('<', v1.email), 0) )) v2(leftArrow)

Upvotes: 2

Related Questions