SQL Basics | Functions | Substring
In SQL, the substring function is used to read part of the data in a field. The function name differs by database.
- MySQL: SUBSTR(), SUBSTRING()
- Oracle: SUBSTR()
- SQL Server: SUBSTRING()
It is generally used as follows. Here, SUBSTR() is used as an example.
SUBSTR(str, pos)
Reads all characters from the pos position in str. Note that this syntax does not apply to SQL Server.
SUBSTR(str, pos, len)
Reads len characters from the pos position in str.
Substring example
Suppose you have the following table.
geography table
| region_name | store_name |
|---|---|
| East | Boston |
| East | New York |
| West | Los Angeles |
| West | San Diego |
Example 1
SELECT SUBSTR(store_name, 3)
FROM geography
WHERE store_name = 'Los Angeles';
The result is as follows.
's Angeles'
Example 2
SELECT SUBSTR(store_name, 2, 4)
FROM geography
WHERE store_name = 'San Diego';
The result is as follows.
'an D'