The SUBSTRING function in SQL queries is most often used when working with text data - it is responsible for "trimming" the string passed to it.
Syntax
As with most programming languages, including ORACLE and MS SQL, SUBSTRING includes three parameters. The first argument of the function is the input string itself - it can be either explicitly registered or obtained as a result of executing some request. Two numerical parameters follow - the start character, from which the crop will occur, and directly the length - the number of characters that you want to read, starting from the start position.
The SQL query structure is as follows:
SUBSTRING ("some string", 1, 3)
The result of this query will be the string "eco" - in SQL, the SUBSTRING function determines elements starting from zero, which, in this example, is the letter "n". It is worth noting that when specifying a negative length, the DBMS will give an error, and when choosing a value that exceeds the difference between the last character number and the start number, the query result will be a chain character from the specified position to the end of the line.
Example
In SQL languages, SUBSTRING is rarely used to perform simple queries - basically, the function is used as part of a complex algorithm. Nevertheless, there are quite simple tasks with her participation. So, for example, if you want to create a category of users similar to the first letter of their last name, then SUBSTRING will do without an auxiliary line break.
SELECT Addres, SUBSTRING (LastName, 1, 1) AS FirstChar FROM Clients
Thus, you can create a simplified telephone directory, where to get the entire list of users whose last names begin with a certain letter, it will be enough to make a selection on the FirstChar field.
A more realistic example is the creation of a ready-made abbreviated version of the username - that is, the last name with the initial of the client should be returned as the result of the request.
SELECT LastName & '' & SUBSTRING (FirstName, 1, 1) & '.' AS Initial FROM Clients
It should be noted that SQL SUBSTRING works equally well with both text and numeric fields.