# SUBSTRING

## Syntax

```
SUBSTRING(str,pos), 
SUBSTRING(str FROM pos), 
SUBSTRING(str,pos,len),
SUBSTRING(str FROM pos FOR len)

SUBSTR(str,pos), 
SUBSTR(str FROM pos), 
SUBSTR(str,pos,len),
SUBSTR(str FROM pos FOR len)
```

## Description

The forms without a *`len`* argument return a substring from string *`str`* starting at position *`pos`*.

The forms with a *`len`* argument return a substring *`len`* characters long from string *`str`*, starting at position *`pos`*.

The forms that use *`FROM`* are standard SQL syntax.

It is also possible to use a negative value for *`pos`*. In this case, the beginning of the substring is *`pos`* characters from the end of the string, rather than the beginning. A negative value may be used for *`pos`* in any of the forms of this function.

By default, the position of the first character in the string from which the substring is to be extracted is reckoned as 1. For [Oracle compatibility](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modeoracle), when `sql_mode` is set to '`oracle`', position zero is treated as position 1 (although the first character is still reckoned as 1).

If any argument is `NULL`, returns `NULL`.

{% tabs %}
{% tab title="Current" %}
The optimizer can take advantage of queries of the format [SUBSTR(col, 1, n) = const\_str](https://mariadb.com/docs/server/reference/sql-functions/string-functions/substring).
{% endtab %}

{% tab title="< 11.8" %}
The optimizer **cannot** take advantage of queries of the format [SUBSTR(col, 1, n) = const\_str](https://mariadb.com/docs/server/reference/sql-functions/string-functions/substring).
{% endtab %}
{% endtabs %}

## Examples

```sql
SELECT SUBSTRING('Knowledgebase',5);
+------------------------------+
| SUBSTRING('Knowledgebase',5) |
+------------------------------+
| ledgebase                    |
+------------------------------+

SELECT SUBSTRING('MariaDB' FROM 6);
+-----------------------------+
| SUBSTRING('MariaDB' FROM 6) |
+-----------------------------+
| DB                          |
+-----------------------------+

SELECT SUBSTRING('Knowledgebase',3,7);
+--------------------------------+
| SUBSTRING('Knowledgebase',3,7) |
+--------------------------------+
| owledge                        |
+--------------------------------+

SELECT SUBSTRING('Knowledgebase', -4);
+--------------------------------+
| SUBSTRING('Knowledgebase', -4) |
+--------------------------------+
| base                           |
+--------------------------------+

SELECT SUBSTRING('Knowledgebase', -8, 4);
+-----------------------------------+
| SUBSTRING('Knowledgebase', -8, 4) |
+-----------------------------------+
| edge                              |
+-----------------------------------+

SELECT SUBSTRING('Knowledgebase' FROM -8 FOR 4);
+------------------------------------------+
| SUBSTRING('Knowledgebase' FROM -8 FOR 4) |
+------------------------------------------+
| edge                                     |
+------------------------------------------+
```

Oracle mode:

```sql
SELECT SUBSTR('abc',0,3);
+-------------------+
| SUBSTR('abc',0,3) |
+-------------------+
|                   |
+-------------------+

SELECT SUBSTR('abc',1,2);
+-------------------+
| SUBSTR('abc',1,2) |
+-------------------+
| ab                |
+-------------------+

SET sql_mode='oracle';

SELECT SUBSTR('abc',0,3);
+-------------------+
| SUBSTR('abc',0,3) |
+-------------------+
| abc               |
+-------------------+

SELECT SUBSTR('abc',1,2);
+-------------------+
| SUBSTR('abc',1,2) |
+-------------------+
| ab                |
+-------------------+
```

## See Also

* [INSTR()](https://mariadb.com/docs/server/reference/sql-functions/string-functions/instr) - Returns the position of a string within a string
* [LOCATE()](https://mariadb.com/docs/server/reference/sql-functions/string-functions/locate) - Returns the position of a string within a string
* [SUBSTRING\_INDEX()](https://mariadb.com/docs/server/reference/sql-functions/string-functions/substring_index) - Returns a string based on substring

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}