Learn Sql Server   Learn Sql Theory   Learn Sql

Chapter 1

Learn Sql Server

Select Statement

Sql Server Order By

Sql Server Asc Desc

Sql Server Where

Sql Server Operators

Sql Server Create Table

Sql Server Insert Statement

Sql Server Update

Sql Server Delete

Sql Server Count

Sql Server NULL

Sql Server RowCount

Sql Server SUM

Sql Server Average

Sql Server MIN MAX

Sql Server ROUND

Sql Server Ceiling

Sql Server Floor

Sql Server Sqrt

Sql Server Top

Sql Server BOTTOM

Sql Server Tie

Sql Server Distinct



Sql Server String Concatenation

Sql Server Data Types

Chapter 2
Chapter 3
Chapter 4
Chapter 5
Chapter 6
Chapter 7
Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 15
Chapter 16

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]
FROM tbl_Employee

This query returns:

Names Middle of Names
Sumon Bagui umon
Sudip Baguiudip
Priyashi Saha riya
Ed Evansd Ev
Genny Georgeenny

(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:

SELECT names, "first letter of names" =
FROM tbl_Employee

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)
FROM tbl_Employee

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.

Suggested topic


Returning Returning a Chunk of an Expression using substring

Sponsored Links