SQL Server One column in a table contains multiple email addresses. How to add new rows to the table for each email address found in the column?

lrpiutwd 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(151)

The emails in the column are formatted like this. Comma delimited, quote encapsulated, with surrounding brackets:

["1stemail@gmail.com","2ndemail@gmail.com","3rdemail@gmail.com"]

The number of emails in the column is variable. This column can have zero emails or hundreds of emails.

Looking to add a new row to the table for each email in this column, if an email exists in the column.

I am admittedly in over my head. I don't know how to do this but I would like to learn.

Each row will contain an identity column.

My first thought was to find how many emails are in the email column by counting the @ characters.

SELECT 
     [email_column], 
     LEN([email_column]) - LEN(REPLACE([email_column], '@', '')) as cnt
  FROM tablename
  ORDER BY cnt DESC

I thought I'd try to use a WHILE loop using the cnt to extract each email from each row. This is where I get in trouble. I don't know if I need to first add a new email column for the max amount of emails found in the statement above, then use a loop to extract the existing emails from the original email column, placing them singly in the added email columns, and then UNPIVOT?

Or is it possible to UNPIVOT without first separating the emails into separate columns?

sql-server

来源：https://stackoverflow.com/questions/76712349/one-column-in-a-table-contains-multiple-email-addresses-how-to-add-new-rows-to

1条答案

按热度按时间

0ejtzxu11#

Instead of looping, I suggest using cross apply with openjson :

First, create and populate sample table (Please save us this step in your future questions):

CREATE TABLE tablename   (
  Id int identity(1,1),
  email_column nvarchar(4000)
);

INSERT INTO tablename (email_column) VALUES
('["1stemail@gmail.com","2ndemail@gmail.com","3rdemail@gmail.com"]'),
(''),
(null),
('["1stemail@gmail.com"]');

Then, create another table to hold the properly-normalized data:

CREATE TABLE emailAddresses(
  emailAddress nvarchar(1000), 
  originalId int
);

And populate it using insert...select :

INSERT INTO emailAddresses(originalId, emailAddress) 
SELECT Id, productTypes.value
FROM tablename 
CROSS APPLY OPENJSON(NULLIF(email_column, '')) AS productTypes;

*Note the usage of NULLIF in the query above, this will prevent errors in your query if email_column contains an empty string.

Results:

emailAddress        originalId
1stemail@gmail.com  1
2ndemail@gmail.com  1
3rdemail@gmail.com  1
1stemail@gmail.com  4

You can see a live demo in db<>fiddle

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server One column in a table contains multiple email addresses. How to add new rows to the table for each email address found in the column?

1条答案

相关问题

热门标签

最新问答