Monday, May 1, 2017

SUBSTRING() in SQL Server


SQL SUBSTRING() function is used to extract a substring from a given string, starting from any position within the string to number of characters specified. SUBSTRING() takes three parameters i.e. string, starting position and no of characters as input and returns the substring based on the parameters. SUBSTRING() does not works on NULL Values, NULL values are not considered. SUBSTRING() works only with text data type and if it is applied on other data types, it returns an error.


Syntax:


SELECT SUBSTRING(expression, starting_position, number_of_characters)


expression is the text whose substring is to be extracted.

starting_position is the position from where substring is to be extracted.

number_of_characters is a positive string which specifies how many characters of given string will be returned.



Example 1:
SUBSTRING() on text column.

SELECT * FROM DatabaseLog



SELECT DISTINCT [Event], SUBSTRING([Event], 5, 5) FROM TempDatabaseLog



Example 2:
SUBSTRING() on column with NULL values.

SELECT * FROM DatabaseLog



SELECT DISTINCT [Event], SUBSTRING([Event], 5, 5) FROM TempDatabaseLog



Example 3:
SUBSTRING() with starting position more than string length.

          SELECT DISTINCT [Event], SUBSTRING([Event], 12, 5) FROM TempDatabaseLog



Example 4:
SUBSTRING() on numeric column.

          SELECT * FROM TempEmployeePayHistory



          SELECT Rate, SUBSTRING(Rate, 3, 2) FROM TempEmployeePayHistory



Share This
Previous Post
Next Post

TekGrabs, established in 2017, is in the business of sharing Information. It provides news, articles and photos about the different technologies, inventions in different domains. Provides information on programming and databases.

0 comments: