Here, dbo.Tally table is for looping through the string values and #ComplanyInformation temp table is having sample data..
--Create a tally table. All Hail Jeff Moden!
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC
--Now use the tally table to extract the relevant information.
create table #CompanyInformation
(
Id int,
String varchar(1000)
);
insert into #CompanyInformation values (1, 'santosh kumar this saaksa santosh@gmail.com 9902007445 testing tet 9765555512 fdfd 43434343 ');
insert into #CompanyInformation values (2, 'DEF Company jr68222@gmail.com 123 Main St Floor 4');
SELECT
Id
,SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) Emails
FROM
dbo.Tally t
cross join #CompanyInformation p
WHERE
N < LEN(' ' + p.String + ' ')
AND SUBSTRING(' ' + p.String + ' ', N, 1) = ' '
AND SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) LIKE '%@%.%'
/* Email of each user:
Id Emails
1 santosh@gmail.com
2 jr68222@gmail.com
*/
SELECT
Id
,SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) PhoneNumbers
FROM
dbo.Tally t
cross join #CompanyInformation p
WHERE
N < LEN(' ' + p.String + ' ')
AND SUBSTRING(' ' + p.String + ' ', N, 1) = ' '
AND SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) NOT LIKE '%[^0-9]%' AND LEN(SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1)) > 9
/* Phone Numbers:
Id PhoneNumbers
1 9902007445
1 9765555512
*/
drop table #CompanyInformation;