SUBSTRING function in MSSQL and MySQL

The SUBSTRING function is used to extract a part of a string. This function is available in both MS SQL Server and MySQL. The MySQL SUBSTRING function provides more functionality than MS SQL Server SUBSTRING function. In MySQL you can also use SUBSTR function which is Synonym for SUBSTRING function

DIFFERENCE between MSSQL SUBSTRING and MySQL SUBSTRING function

MySQL SUBSTRING function accepts negative values for start argument. If negative value is specified MySQL function returns N number of characters from right side of the string. The MSSQL SUBSTRING function does not provide this functionality

Also in MySQL if we don't pass any value for length argument it returns the substring from START position to the end of the string

Here is the Syntax and Examples of this function in MSSQL and MySQL

 

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
Is an integer expression that specifies where the returned characters start.

length
Is a positive integer expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated.

The values for start and length must be specified in number of characters for ntext, char, or varchar data types and bytes for text, image, binary, or varbinary data types

Examples

The following query returns 4 characters starting from 3 position from the string 'abcdefgh'

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

The following query returns the initial character of firstname from employees table

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

(No column name) 	Lastname
N 			Davolio
A 			Smythe
J			Leverling
M 			Peacock
S 			Buchanan
M 			Smith
R 			King
L 			Callahan
A 			Dodsworth

MySQL

The MySQL SUBSTRING function provides the same functionality as MSSQL SUBSTRING function. i.e. it returns a part of the string.

Here is the Syntax and Examples

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 from which the sub string is sought

len
Is the number of characters from pos to be returned

The FROM and FOR keywords are optional.

Example

The following query returns 4 characters starting from 3 position from the string 'abcdefgh'

mysql> select SUBSTRING('abcdefgh',3,4);
+---------------------------+
| SUBSTRING('abcdefgh',3,4) |
+---------------------------+
| cdef |
+---------------------------+
1 row in set (0.00 sec)

mysql>

 

In MySQL you can also pass negative value for START argument, if we pass negative value it will return the N number of character from right side as shown in the following example

mysql> select SUBSTRING('abcdefgh',-4,3);
+----------------------------+
| SUBSTRING('abcdefgh',-4,3) |
+----------------------------+
| efg |
+----------------------------+
1 row in set (0.00 sec)

mysql>

The following query returns the initial character of firstname from employees table

mysql> select SUBSTRING(firstname,1,1),lastname from employees;
+--------------------------+-------------+
| SUBSTRING(firstname,1,1) |	lastname |
+--------------------------+-------------+
| N | 				Davolio	 |
| A | 				Smythe	 |
| J |				Leverling|
| M | 				Peacock	 |
| S | 				Buchanan |
| M | 				Smith	 |
| R | 				King	 |
| L | 				Callahan |
| A | 				Dodsworth|
| N | 				Davolio	 |
| A | 				Smythe	 |
| J | 				Leverling|
| M | 				Peacock	 |
| S | 				Buchanan |
| M | 				Smith	 |

You can also use CONCAT function along with SUBSTRING function to produce a formatted output.

mysql> select concat(substr(firstname,1,1),'. ',lastname) as EmpName from employees;
+----------------+
| EmpName   	 |
+----------------+
| N. Davolio 	 |
| A. Smythe  	 |
| J. Leverling   |
| M. Peacock 	 |
| S. Buchanan 	 |
| M. Smith 	 |
| R. King 	 |
| L. Callahan 	 |
| A. Dodsworth 	 |
| N. Davolio 	 |
| A. Smythe 	 |
| J. Leverling 	 |
| M. Peacock 	 |
| S. Buchanan 	 |
| M. Smith 	 |
| R. King 	 |

Also in MySQL if we don't pass any value for length argument it returns the substring from START position to the end of the string

mysql> select SUBSTR('abcdefgh',3);
+----------------------+
| SUBSTR('abcdefgh',3) |
+----------------------+
| cdefgh |
+----------------------+
1 row in set (0.00 sec)

Apart from SUSBTRING function MySQL also provides SUBSTRING_INDEX function which returns words occurring between delimiter

SUBSTRING_INDEX

Syntax

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned.

Examples

The following returns 2 words occurring within "." delimiter

mysql> select SUBSTRING_INDEX('www.dbload.com','.',2);
+-----------------------------------------+
| SUBSTRING_INDEX('www.dbload.com','.',2) |
+-----------------------------------------+
| www.dbload |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql>

If we give negative number then it returns words from right side as shown below

mysql> select SUBSTRING_INDEX('www.dbload.com','.',-2);
+------------------------------------------+
| SUBSTRING_INDEX('www.dbload.com','.',-2) |
+------------------------------------------+
| dbload.com |
+------------------------------------------+
1 row in set (0.00 sec)

mysql>

 

Back to Converting Functions from MSSQL to MySQL