# SUBSTRING

Extracts a range of characters from a string.

## Syntax

```sql
SUBSTRING(str, start, len)
SUBSTR(str, start, len)
SUBSTRING(str, start)
SUBSTR(str, start)
SUBSTRING(str FROM start FOR len)
SUBSTR(str FROM start FOR len)
SUBSTRING(str FROM start)
SUBSTR(str FROM start)

```

## Arguments

* str: any string or binary object
* start: the one-indexed position to start at. If negative, counts from the end.
* len: length is the number of characters to extract. Forms without `len`, extract characters until the end of the string.

## Return Type

String

## Remarks

* In `SUBSTR` or `SUBSTRING`, the starting index point of a string is `1` (`0` is an alias for `1`).

## Examples

In the following example, the starting index `3` represents the third character in the string, because the index starts from `1`.

```sql
SELECT SUBSTRING('1234567890', 3, 3);

```

```output

+-------------------------------+
| SUBSTRING('1234567890', 3, 3) |
+-------------------------------+
| 345                           |
+-------------------------------+
```

In the following example, the substring is returned from the end, because the starting index is a negative number.

```sql
SELECT SUBSTRING('1234567890', -3, 3);

```

```output

+--------------------------------+
| SUBSTRING('1234567890', -3, 3) |
+--------------------------------+
| 890                            |
+--------------------------------+

```

The following examples demonstrate how substring works using different lengths and starting positions. The table and values for these examples are included.

```sql
CREATE TABLE sub_tab(a INT, b INT, c INT, d INT);

INSERT INTO sub_tab VALUES(1234,1234,1234,1234),(1,2,4, NULL);

```

```sql
SELECT substr(a,0,1) FROM sub_tab;


```

```output

+---------------+
| substr(a,0,1) |
+---------------+
| 1             |
| 1             |
+---------------+
```

```sql
SELECT substr(a,1,1) FROM sub_tab;


```

```output

+---------------+
| substr(a,1,1) |
+---------------+
| 1             |
| 1             |
+---------------+
```

```sql
SELECT substr(a,1,2) FROM sub_tab;


```

```output

+---------------+
| substr(a,1,2) |
+---------------+
| 1             |
| 12            |
+---------------+
```

```sql
SELECT substr(a,0,2) FROM sub_tab;


```

```output

+---------------+
| substr(a,0,2) |
+---------------+
| 12            |
| 1             |
+---------------+
```

> **⚠️ Warning**: ## Implicit CollationWhen `character_set_server` is set to `utf8`, string literals with characters using 4-byte encoding are implicitly assigned binary collation and processed as a sequence of bytes rather than characters. This implicit conversion to binary collation causes string functions to return unexpected results. To avoid using implicit binary collation, either use explicit type casting or use database columns defined with the `utf8mb4` character set. For more information, refer to [Implicit Collation in Special Cases](https://docs.singlestore.com/db/v9.1/reference/sql-reference/character-encoding/special-cases.md).

## Related Topics

* [SUBSTRING\_INDEX](https://docs.singlestore.com/db/v9.1/reference/sql-reference/string-functions/substring-index.md)

***

Modified at: June 12, 2023

Source: [/db/v9.1/reference/sql-reference/string-functions/substring/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/string-functions/substring/)

(An index of the documentation is available at /llms.txt)