SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #Mail(
	[Name] [nvarchar](50) NOT NULL,
	[Surname] [nvarchar](50) NOT NULL,
	[Email_adress] [nvarchar](500) NULL,
) ON [PRIMARY];
INSERT INTO #Mail
  ( [Name], [Surname], [Email_adress])
VALUES
  ('Mary', 'Green', 'mary.green@greenmail.com'),
  ('Lory', 'Red', 'lory@redmail.com'),
  ('John', 'Yellow', 'j.yellow@mail.ma'),
  ('Susan', 'De Blue', 'susy@bluemail.com');
4 rows affected

SELECT *,CHARINDEX('@',[Email_adress]) AS [Position],
LEFT([Email_adress],CHARINDEX('@',[Email_adress])-1) AS [Mail_username]
FROM #Mail





Name
Surname
Email_adress
Position
Mail_username


Mary
Green
mary.green@greenmail.com
11
mary.green

Lory
Red
lory@redmail.com
5
lory

John
Yellow
j.yellow@mail.ma
9
j.yellow

Susan
De Blue
susy@bluemail.com
5
susy

Name	Surname	Email_adress	Position	Mail_username
Mary	Green	mary.green@greenmail.com	11	mary.green
Lory	Red	lory@redmail.com	5	lory
John	Yellow	j.yellow@mail.ma	9	j.yellow
Susan	De Blue	susy@bluemail.com	5	susy