Optimizing a Query for splitting strings with a known size
Instead of:

SELECT
  Col1 = (SELECT Val from dbo.split(Name, '.') WHERE Id = '1'),
  Col2 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '2'),
  Col3 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '3')
FROM Mains


Use:

SELECT
  s.*
FROM Mains
CROSS APPLY (
    SELECT
        MAX(CASE WHEN Id = 1 THEN Val END) AS Col1,
        MAX(CASE WHEN Id = 2 THEN Val END) AS Col2,
        MAX(CASE WHEN Id = 3 THEN Val END) AS Col3
    FROM dbo.split(Name,'.') s
    ) s 


The idea is that you still want exactly one row per row in Mains. And using an aggregate function inside CROSS APPLY will do just that. By using CASE you only need call split() once per row.

There are plenty of questions around here about splitting strings, and SQL DB does already have a string_split() function built in.