Русский    English

SUBSTRING function

The  SUBSTRING (expression , start , length) function allows us to extract from an expression its part of a pecified length, starting from a specified initial position (start). Expression may be a character or a binary string, and also can have a text or image type. For example, if we need to get 3 characters in a ship name, starting from the 2nd character, then it's not quite easy to do it without the SUBSTRING function. And so we write:

  1. SELECT name, SUBSTRING(name, 2, 3)
  2. FROM Ships

In case we need to extract all the characters, starting from the certain, we also can use this function. For example,

  1. SELECT name, SUBSTRING(name, 2, LEN(name))
  2. FROM Ships
will give us all the characters in the names of the ships, starting from the second letter in the name. Pay attention that in order to specify the number of characters to extract I used the LEN(name) function that returns a number of characters in the name. It's clearly that forasmuch as I need the characters, starting from the second, its number will be less than the whole number of the characters in the name. But it doesn't cause an error, because if a specified number of characters exceed a permissible number, all the characters until the end of a string will be extracted. That is why I take it with a reserve sparing myself the calculations.

Suggested exercises: 136

Bookmark and Share

aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100