T-SQL - Built-in Functions - String Functions - SUBSTRING() - How to return a portion of a string from Table
|
Mayıs 11, 2011 by Microsoft
|
|
This topic contains and tries to cover following subjects
- Brief explanation of SUBSTRING () function in SQL
- Syntax of SUBSTRING () function in SQL
- Example to SUBSTRING () function in SQL
Articles contains and tries to provide answer to following questions and issues:
- What is SUBSTRING () function..
- How to use SUBSTRING () function..
- How to select a part of text from table in SQL..
- How to return a specified length of text from table in SQL...
Articles may require following knowledge:
- Basic query flow in SQL
- SQL Server Management Studio
- SQL server string functions
Explanation of SUBSTRING () function in SQL
In SQL we can sometimes need to return a part of string , a specified length of text with query. For example, assume that we have a column to store SQL news, and we need to provide a brief view of news in a main page. To achieve that we can use substring in our query to return a specified length of text from beginning of the text or a place random within the text data.
Substring function accepts three parameter:
1. source string
2. beginning position
3. length
Source string is provided
1. char , varchar , text.
2. nchar , nvarchar , ntext
When we supply source type from above types indicated by 1, returning data type is varchar . For 2nd type source string s supplied, data is returned as nvarchar .
After source text, we supply where to start for select as position of text, and lastly length is provided.
If we have a text like 400 char length, and if we provide length as 500: all text is selected. In that case, SQL does not generate an error. It may help when you create temporarily the table in SQL, or cases that is not possible to foreseen the max value of column.
Syntax of SUBSTRING () function in SQL
Syntax for substring is as follow:
SELECT SUBSTRING
(
'source text',
where to start (number),
length (number)
);
Example of SUBSTRING () function in SQL
To demonstrate how substring works, we will use it in a simple select statement. We will supply a text like "SQL server 2012 is announced. Some details...", and we will select first 20 char acter from beginning.
Query is as follows:
As above result indicates, we returned 20 char acter from beginning. In most cases, a column name is supplied as source when you indicate WHERE clause to work with the date which is dynamically supplied to query.
Data Layers
| Area: | programming \ languages \ tsql \ \ \ |
| Ref: | http://msdn.microsoft.com/en-us/library/ms187748.aspx |
| Loc: | articles |
| Tags: | tsql |
|