sql_func_mid

SQL SUBSTRING() Function (and MID())

Extracting Substrings from Text

The SUBSTRING() function is the standard SQL way to extract a portion of a string (a substring) from a text field. Some database systems, like MySQL and MS Access, also offer MID() as an equivalent or similar function.

SQL SUBSTRING() Syntax

SELECT SUBSTRING(string_column, start_position, length)
AS alias_name
FROM table_name;

Parameter	Description
string_column	Required. The column containing the text or the literal string from which to extract.
start_position	Required. An integer specifying the starting position for extraction. The first character in the string is position 1.
length	Required (in standard SQL, though some dialects make it optional). The number of characters to extract. If omitted in dialects that allow it, it usually extracts to the end of the string.

Note on MID():

If you are using MySQL or MS Access, you might also use MID():

SELECT MID(column_name, start_position, length)
AS alias_name
FROM table_name;

For portability across different SQL database systems, SUBSTRING() is generally preferred.

Demo Database: Northwind

We’ll use the Northwind sample database. Consider the “Customers” table:

CustomerID | CustomerName                      | City
-----------|-----------------------------------|---------
1          | Alfreds Futterkiste             | Berlin
2          | Ana Trujillo Emparedados y helados| México D.F.
3          | Antonio Moreno Taquería         | México D.F.
4          | Around the Horn                   | London
5          | Berglunds snabbköp              | Luleå
...

SQL SUBSTRING() / MID() Example

The following SQL statement extracts the first four characters from the “City” column in the “Customers” table:

Using SUBSTRING() (Standard):

SELECT SUBSTRING(City, 1, 4) AS ShortCity
FROM Customers;

Using MID() (e.g., for MySQL):

SELECT MID(City, 1, 4) AS ShortCity
FROM Customers;

Result for both queries (first few rows):

ShortCity
---------
Berl
Méxi
Méxi
Lond
Lule
...

Practical Application:

Extracting parts of strings is useful for:

• Generating abbreviations or codes.
• Parsing data stored in a fixed format within a single string (e.g., extracting year from a YYYYMMDD date string).
• Data cleaning and transformation tasks.

For more advanced string manipulation, explore functions like LEFT(), RIGHT(), LENGTH(), CHARINDEX() or POSITION(), and REPLACE() available in your specific SQL dialect.