In SQL Server environments, two of the many string functions at our disposal are LEFT() and SUBSTRING().

These functions do a similar thing, but there are differences. This article looks at some of the main differences between these functions.

Defintion

First, let’s look at what each function does:

LEFT()

Returns the left part of a character string with the specified number of characters.

SUBSTRING()

Returns part of a character, binary, text, or image expression.

So LEFT() returns only the left part of the string.

SUBSTRING() simply returns a part of the expression (it’s not limited to just the left part – it could be left, right or somewhere in the middle).

Example 1 – Same Result

Both functions can be used to return a certain number of characters from the left.

SELECT 
  LEFT('Buckethead', 6) AS [LEFT],
  SUBSTRING('Buckethead', 1, 6) AS [SUBSTRING];

Result:

+--------+-------------+
| LEFT   | SUBSTRING   |
|--------+-------------|
| Bucket | Bucket      |
+--------+-------------+

However, LEFT() is more concise. This is partly due to the fact that there are less characters in the function name.

But it’s also because of the syntax. The LEFT() function only requires two arguments (the expression, and the number of characters to return), whereas SUBSTRING() requires three arguments (the expression, the starting position, and the number of characters to return from that starting position).

Example 2 – When SUBSTRING is Better

SUBSTRING() is much better than LEFT() at grabbing data from the middle of the string. Actually, left isn’t designed for that. If you really wanted to use LEFT() to do this, you’d need to do some trickery, such as use a RIGHT() function as its first argument.

Example:

SELECT 
  LEFT(RIGHT('Buckethead', 7), 3) AS [LEFT/RIGHT],
  SUBSTRING('Buckethead', 4, 3) AS [SUBSTRING];

Result:

+--------------+-------------+
| LEFT/RIGHT   | SUBSTRING   |
|--------------+-------------|
| ket          | ket         |
+--------------+-------------+

Accepted Data Types

The LEFT() function doesn’t accept the text and ntext data types as its first argument. It supports all other data types that can be converted to varchar or nvarchar.

The SUBSTRING() function on the other hand, accepts character, binary, text, ntext, and image expressions.

Return Values

LEFT() returns varchar when the first argument is a non-Unicode character data type, and nvarchar when it’s a Unicode character data type.

The return values for SUBSTRING() are as follows:

Expression (first argument)	Return type
char/varchar/text	varchar
nchar/nvarchar/ntext	nvarchar
binary/varbinary/image	varbinary