SUBSTRING Function in MSSQL and MySQL


SUBSTRING extracts a part of a string. It's available in both MS SQL Server and MySQL, but MySQL's version provides more functionality. In MySQL you can also use SUBSTR, a synonym for SUBSTRING.

Key Difference

MySQL's SUBSTRING accepts negative values for the start argument — a negative value returns N characters from the right side of the string. MSSQL doesn't support this. Also, in MySQL, omitting the length argument returns the substring from the start position to the end of the string.

See also: Top tools for converting MS SQL Server to MySQL

MS SQL Server

Syntax:

SUBSTRING ( expression, start, length )

Where expression is a character, binary, text, ntext, or image expression; start specifies where the returned characters start; and length is a positive integer specifying how many characters to return (a negative length generates an error).

Example — return 4 characters starting from position 3 in 'abcdefgh':

SELECT SUBSTRING('abcdefgh',3,4);
----------------------
cdef

MSSQL SUBSTRING Example

Return the initial character of firstname from the employees table:

SELECT SUBSTRING(firstname,1,1), Lastname from employees;

MSSQL SUBSTRING function Example

MySQL

Syntax:

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

Where str is the string expression, pos is the start position, and len is the number of characters to return. FROM/FOR are optional keywords.

Example — same result as the MSSQL example above:

mysql> select SUBSTRING('abcdefgh',3,4);
+----------------------------+
| SUBSTRING('abcdefgh',3,4)  |
+----------------------------+
| cdef                       |
+----------------------------+

MySQL Substring Function Example

Pass a negative value for the START argument to return N characters from the right:

mysql> select SUBSTRING('abcdefgh',-4,3);
+-----------------------------+
| SUBSTRING('abcdefgh',-4,3)  |
+-----------------------------+
| efg                         |
+-----------------------------+

MySQL Substring negative start example

You can combine CONCAT with SUBSTRING for formatted output:

mysql> select concat(substr(firstname,1,1),'. ',lastname) as EmpName from employees;
+----------------+
| EmpName        |
+----------------+
| N. Davolio     |
| A. Smythe      |
| J. Leverling   |
+----------------+

MySQL Substring formatted output example

Omitting the length argument returns the substring from the start position to the end of the string:

mysql> select SUBSTR('abcdefgh',3);
+-----------------------+
| SUBSTR('abcdefgh',3)  |
+-----------------------+
| cdefgh                |
+-----------------------+

MySQL Substr Function Example

SUBSTRING_INDEX

MySQL also provides SUBSTRING_INDEX, which returns words occurring before a given number of delimiter occurrences.

Syntax:

SUBSTRING_INDEX(str, delim, count)

Returns the substring from str before count occurrences of delim. If count is positive, everything to the left of the final delimiter is returned; if negative, everything to the right.

Example — return 2 words from the "." delimiter:

mysql> select SUBSTRING_INDEX('www.dbload.com','.',2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.dbload.com','.',2)    |
+-------------------------------------------+
| www.dbload                                 |
+-------------------------------------------+

MySQL SUBSTRING_INDEX example 1

A negative count returns words from the right:

mysql> select SUBSTRING_INDEX('www.dbload.com','.',-2);
+---------------------------------------------+
| SUBSTRING_INDEX('www.dbload.com','.',-2)      |
+---------------------------------------------+
| dbload.com                                    |
+---------------------------------------------+

MySQL SUBSTRING_INDEX example 2

Back to Converting Functions from MSSQL to MySQL