CODEWITHSUNDEEP
sqlsql-serversplit
corr
corr

Reputation: 73

Evaluating a column using STRING_SPLIT()

I have a table where one columns contains several email addresses or names in one string:

+----+-------------------------------------------------------+
| id ¦ emails                                                ¦
+----+-------------------------------------------------------+
| 1  ¦ John Doe; [email protected]; Company IT; [email protected]; ¦
+----+-------------------------------------------------------+
| 2  ¦ [email protected]; Jane Doe; [email protected];           ¦
+----+-------------------------------------------------------+

I need to evaluate the emails column to identify whether there are external email addresses (e.g. [email protected]). So, I need to split the values in the column emails into individual strings and evaluate each string. In the end, I'd like to update the table with an additional column indicating whether the emails are internal or external. 

+----+-------------------------------------------------------+-----------+
| id ¦ emails                                                ¦ flag      ¦
+----+-------------------------------------------------------+-----------+
| 1  ¦ John Doe; [email protected]; Company IT; [email protected]; ¦ external  ¦
+----+-------------------------------------------------------+-----------+
| 2  ¦ [email protected]; Jane Doe; [email protected];           ¦ internal  ¦
+----+-------------------------------------------------------+-----------+

I managed to split a string using STRING_SPLIT() and to perform the evaluation I'd like (of a sort).

DECLARE @flag int

SELECT @flag=COUNT(*) FROM STRING_SPLIT('John Doe; [email protected]; Company IT; [email protected];', ';')
WHERE value LIKE '%@%'AND value NOT LIKE '%Company%'

IF (@flag > 0)
BEGIN
Print 'extern'
END
ELSE 
BEGIN
Print 'intern'
END

However, now I am passing a string to the STRING_SPLIT() function. I'd like to pass the column emails instead and update the table according to the results of the evaluation. Any ideas how to achieve this?

Upvotes: 3

Views: 193

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You can do this in a select as:

select t.*,
       (case when e.cnt > 0 then 'external' else 'internal' end) as flag
from t cross apply
     (select count(*) as cnt
      from string_split(t.emails, ';') s
      where value LIKE '%@%' AND value NOT LIKE '%Company%'
     ) e;

Note: this keeps the OP's original logic for identifying "internal" versus "external". The question does not seem to be about that. Obviously, this version confuses the username and domain components, so the username could actually contain the company name, even though the OP might want it as "external".

Normally, I would warn about storing multiple values in a single column, recommending a more normalized structure -- a table with one email per row. I do understand that sometimes email list are used just to pass to email programs and need to be in a particular format. In such cases, they are usually treated as black boxes, and not typically parsed in SQL, so they are a reasonable exception to the rule.

Upvotes: 2

Related Questions