IF OBJECT_ID('Tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable
(
String_Value NVARCHAR(40)
)

DECLARE @i as INT
set @i = 1

WHILE  @i <= 10
BEGIN
	DECLARE @New_Email AS NVARCHAR(40)
	SET @New_Email = REPLACE(NEWID(), '-','') + '@mail.ru'		
		INSERT INTO #TempTable SELECT @New_Email
	SET @i+=1 
END

SELECT
String_Value AS 'Initial string',
SUBSTRING(String_Value, 0, CHARINDEX('@', String_Value)) AS 'Left before @',
SUBSTRING(String_Value, CHARINDEX('@', String_Value) +1, LEN(String_Value)) AS 'Right after @',
SUBSTRING(String_Value, CHARINDEX('@', String_Value) +1, CHARINDEX('.', String_Value) - CHARINDEX('@', String_Value) - 1) AS 'Between DOT and @'
FROM #TempTable AS t