Split an email in a query

Question

I have a Table called user which contain a field name and an email

Email all follow this syntax

[email protected]

And I want to have a query that update my name table so it looks like this

name forname

I already tried to use string_split.

How to split an email address into its parts

I used this ref to understand but the SELECT throw me an error.

The thing is I don't understand how to use it.

If someone could just explain how it works it would be great.

Answer 1

Please try the following solution based on the PARSENAME() function.

So, we will use tokenization method instead of strings parsing.

Assumption is that all emails are strictly following identical four part format as [email protected]

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, name VARCHAR(50), email VARCHAR(128));
INSERT @tbl (name, email) VALUES
('name1', '[email protected]'),
('name2', '[email protected]');
-- DDL and sample data population, end

-- before
SELECT * FROM @tbl;

UPDATE t1
SET name = PARSENAME(tokens, 4) + SPACE(1) + PARSENAME(tokens, 3)
FROM @tbl AS t1
CROSS APPLY (SELECT REPLACE(email,'@', '.')) AS t2(tokens);

-- after
SELECT * FROM @tbl;

Output

id	name	email
1	name forname	[email protected]
2	john doe	[email protected]