SUBSTRING Function In Sql Server
The SUBSTRING in Sql Server function returns part of a string. Following is the format for the SUBSTRING function:
SUBSTRING(stringexpression, startposition, length)
SELECT names, SUBSTRING(names,2,4) AS [middle of names]
This query returns:
| Names || Middle of Names |
|Sumon Bagui ||umon|
|Priyashi Saha|| riya |
|Ed Evans||d Ev|
|Genny George||enny |
(5 row(s) affected)
String expression is the column that we will be using, startposition tells SQL Server where in the stringexpression to start retrieving characters from, and length tells SQL Server how many characters to extract. All three parameters are required in SQL Server 2005's SUBSTRING function.
In the above example SUBSTRING(names,2,4) is started from the second position in the column, names, and extracted four characters starting from position 2.
Strings in SQL Server 2005 are indexed from 1. If you start at position 0, the following query will show you what you will get:
SELECT names, "first letter of names" = SUBSTRING(names,0,2) FROM tbl_Employee
You will get:
| names ||first letter of names|
| Sumon Bagui ||S|
| Sudip Bagui ||S|
| Priyashi Saha || P|
| Ed Evans || E|
| Genny George || G|
(5 row(s) affected)
In the previous output, we got the first letter of the names because the SUBSTRING function started extracting characters starting from position zero (the position before the first letter) and went two character positionswhich picked up the first letter of the names field.
We could have also achieved the same output with:
Here the SUBSTRING function would start extracting characters starting from position 1 and go only one character position, hence ending up with only one characterwhich picks up the first letter of the names field.
SQL Server 2005's SUBSTRING function actually allows you to start at a negative position relative to the string. For example, if you typed:
SELECT names, "first letter of names" = SUBSTRING(names,-1,3)
You would get the same output as the previous query also, because you are starting two positions before the first character of names, and going three character places, so you get the first letter of the name.