There is no difference at all between left and substring because left is translated to substring in the execution plan.

For example:

select substring(col, 1, 2),
       left(col, 3)
from YourTable

will look like this in the execution plan

<DefinedValue>
  <ColumnReference Column="Expr1004" />
  <ScalarOperator ScalarString="substring([col],(1),(2))">
    <Intrinsic FunctionName="substring">
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="col" />
        </Identifier>
      </ScalarOperator>
      <ScalarOperator>
        <Const ConstValue="(1)" />
      </ScalarOperator>
      <ScalarOperator>
        <Const ConstValue="(2)" />
      </ScalarOperator>
    </Intrinsic>
  </ScalarOperator>
</DefinedValue>
<DefinedValue>
  <ColumnReference Column="Expr1005" />
  <ScalarOperator ScalarString="substring([col],(1),(3))">
    <Intrinsic FunctionName="substring">
      <ScalarOperator>
        <Identifier>
          <ColumnReference Column="col" />
        </Identifier>
      </ScalarOperator>
      <ScalarOperator>
        <Const ConstValue="(1)" />
      </ScalarOperator>
      <ScalarOperator>
        <Const ConstValue="(3)" />
      </ScalarOperator>
    </Intrinsic>
  </ScalarOperator>
</DefinedValue>

SQL Server is a database. You dod not ask questions of which string processing function is 'faster'. You ask the questions 'which can use an index?' and 'do I have the required index?'. Is all about data access, because disks are sloooooow, not about shifting CPU registers.

So, Which can use an index? (which one is sargable ?). In theory LEFT could use an index, but in practice it usually does not. SUBSTRING cannot. Instead of SUBSTRING use Full Text .

Design your data model to take advantage of sargable expressions, index accordingly. That's all there is to it, there is no magic bullet. Avoid scans.

This is a good article which benchmarks performance between SUBSTRING, LIKE, CHARINDEX, and LEFT/RIGHT if anyone's interested.

According to the results, on nonindexed columns, LEFT/RIGHT are consistently faster than SUBSTRING.

When you use functions on the predicates, your engine will be forced to use Scan operation over Seek operation. Theoretically Left looks favor of using index smartly. But, your engine still don't know the output of Left() function until it get executed. So, it is same for Substring() also.

If you really want to tune your performance of the query, you can replace Left() expression with LIKE expression. Make sure the % wildcard character at the end. This expression will use Index Seek (if you have appropriate index on the column).
Example,

Left(MyColumn, 2) = 'AB' >> MyColumn LIKE 'AB%'

Actually, The LIKE operator (with the % wildcard character at the end), eventually converted into logical seek predicates by engine. So, the above LIKE expression will be rewritten by engine as follow as,

MyColumn LIKE 'AB%' >> MyColumn >= 'AB' and MyColumn < 'AC'

For Substring() you don't have better replacement and you have to think other alternats, like Full Text.

Table like this

Id_num  Fname    Minit      Lname       ids
1    Karin    F     Josephs      3
2    Pirkko   O     Koskitalo       56
3        Karin    F     Josephs     16
4        Pirkko   O     Koskitalo        96
1        Karin    F     Josephs      3
2    Pirkko   O     Koskitalo        56

Substring:

Using SUBSTRING , provide the initial position value and End position value.

Eg:

select SUBSTRING(Fname,2,5) from new_employees
(No column name)
arin
irkko
arin
irkko
arin
irkko

Left:

Using left give only how many char you want from LEFT Side.

Eg:

select left(Fname,2) from new_employees

(No column name)
Ka
Pi
Ka
Pi
Ka
Pi