> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# STRING

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `STRING` <InternalLink path="data-types">data type</InternalLink> stores a string of Unicode characters.

<Note>
  `STRING` is not a data type supported by PostgreSQL. For PostgreSQL compatibility, CockroachDB supports additional [aliases](#aliases) and [`STRING`-related types](#related-types).
</Note>

## Aliases

CockroachDB supports the following alias for `STRING`:

* `TEXT`

## Related types

For PostgreSQL compatibility, CockroachDB supports the following `STRING`-related types and their aliases:

* `VARCHAR` (and alias `CHARACTER VARYING` )
* `CHAR` (and alias `CHARACTER` )

These types are functionality identical to `STRING`.

CockroachDB also supports the single-byte `"char"` special character type. As in PostgreSQL, this special type is intended for internal use in <InternalLink path="system-catalogs">system catalogs</InternalLink>, and has a storage size of 1 byte. CockroachDB truncates all values of type `"char"` to a single character.

## Length

To limit the length of a string column, use `STRING(n)`, where `n` is the maximum number of Unicode code points (normally thought of as "characters") allowed. This applies to all related types as well (e.g., to limit the length of a `VARCHAR` type, use `VARCHAR(n)`). To reduce performance issues caused by storing very large string values in indexes, Cockroach Labs recommends setting length limits on string-typed columns.

We **strongly recommend** adding size limits to all <InternalLink path="indexes">indexed columns</InternalLink>, which includes columns in <InternalLink path="primary-key">primary keys</InternalLink>.

Values exceeding 1 MiB can lead to <InternalLink path="architecture/storage-layer">storage layer write amplification</InternalLink> and cause significant performance degradation or even <InternalLink path="cluster-setup-troubleshooting#out-of-memory-oom-crash">crashes due to OOMs (out of memory errors)</InternalLink>.

To add a size limit using <InternalLink path="create-table">`CREATE TABLE`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE name (first STRING(100), last STRING(100));
```

To add a size limit using <InternalLink path="alter-table#alter-column">`ALTER TABLE... ALTER COLUMN`</InternalLink>:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET enable_experimental_alter_column_type_general = true;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ALTER TABLE name ALTER first TYPE STRING(99);
```

When inserting a `STRING` value or a `STRING`-related-type value:

* If the value is cast with a length limit (e.g., `CAST('hello world' AS STRING(5))` ), CockroachDB truncates to the limit. This applies to `STRING(n)` and all related types.
* If the value exceeds the column's length limit, CockroachDB returns an error. This applies to `STRING(n)` and all related types.
* For `STRING(n)` and `VARCHAR(n)` / `CHARACTER VARYING(n)` types, if the value is under the column's length limit, CockroachDB does **not** add space padding to the end of the value.
* For `CHAR(n)`/`CHARACTER(n)` types, if the value is under the column's length limit, CockroachDB adds space padding from the end of the value to the length limit.

| Type                                              | Length                         |
| ------------------------------------------------- | ------------------------------ |
| `CHARACTER`, `CHARACTER(n)`, `CHAR`, `CHAR(n)`    | Fixed-length                   |
| `CHARACTER VARYING(n)`, `VARCHAR(n)`, `STRING(n)` | Variable-length, with a limit  |
| `TEXT`, `VARCHAR`, `CHARACTER VARYING`, `STRING`  | Variable-length, with no limit |
| `"char"` (special type)                           | 1 byte                         |

## Syntax

A value of type `STRING` can be expressed using a variety of formats. See <InternalLink path="sql-constants#string-literals">string literals</InternalLink> for more details.

When printing out a `STRING` value in the <InternalLink path="cockroach-sql">SQL shell</InternalLink>, the shell uses the simple SQL string literal format if the value doesn't contain special character, or the escaped format otherwise.

### Collations

`STRING` values accept <InternalLink path="collate">collations</InternalLink>, which lets you sort strings according to language- and country-specific rules.

## Size

The size of a `STRING` value is variable, but it's recommended to keep values under 64 kilobytes to ensure performance. Above that threshold, <InternalLink path="architecture/storage-layer">write amplification</InternalLink> and other considerations may cause significant performance degradation.

## Examples

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE strings (a STRING PRIMARY KEY, b STRING(4), c TEXT);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW COLUMNS FROM strings;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
--------------+-----------+-------------+----------------+-----------------------+-----------+------------
  a           | STRING    |    false    | NULL           |                       | {primary} |   false
  b           | STRING(4) |    true     | NULL           |                       | {primary} |   false
  c           | STRING    |    true     | NULL           |                       | {primary} |   false
(3 rows)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO strings VALUES ('a1b2c3d4', 'e5f6', 'g7h8i9');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM strings;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
     a     |  b   |   c
+----------+------+--------+
  a1b2c3d4 | e5f6 | g7h8i9
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE aliases (a STRING PRIMARY KEY, b VARCHAR, c CHAR);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW COLUMNS FROM aliases;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  column_name | data_type | is_nullable | column_default | generation_expression |   indices   | is_hidden
+-------------+-----------+-------------+----------------+-----------------------+-------------+-----------+
  a           | STRING    |    false    | NULL           |                       | {primary}   |   false
  b           | VARCHAR   |    true     | NULL           |                       | {primary}   |   false
  c           | CHAR      |    true     | NULL           |                       | {primary}   |   false
(3 rows)
```

## Supported casting and conversion

`STRING` values can be <InternalLink path="data-types#data-type-conversions-and-casts">cast</InternalLink> to any of the following data types:

| Type        | Details                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| ----------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `ARRAY`     | Requires supported <InternalLink path="array">`ARRAY`</InternalLink> string format, e.g., `'{1,2,3}'`. Note that string literals can be implicitly cast to any supported `ARRAY` data type except <InternalLink path="bytes">`BYTES`</InternalLink>, <InternalLink path="enum">`ENUM`</InternalLink>, <InternalLink path="jsonb">`JSONB`</InternalLink>, <InternalLink path="serial">`SERIAL`</InternalLink>, and the <InternalLink path="architecture/glossary#data-types">spatial data types</InternalLink>`Box2D`, `GEOGRAPHY`, and `GEOMETRY`. |
| `BIT`       | Requires supported <InternalLink path="bit">`BIT`</InternalLink> string format, e.g., `'101001'` or `'xAB'`.                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| `BOOL`      | Requires supported <InternalLink path="bool">`BOOL`</InternalLink> string format, e.g., `'true'`.                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `BYTES`     | For more details, <InternalLink path="bytes#supported-conversions">see here</InternalLink>.                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| `DATE`      | Requires supported <InternalLink path="date">`DATE`</InternalLink> string format, e.g., `'2016-01-25'`.                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| `DECIMAL`   | Requires supported <InternalLink path="decimal">`DECIMAL`</InternalLink> string format, e.g., `'1.1'`.                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| `FLOAT`     | Requires supported <InternalLink path="float">`FLOAT`</InternalLink> string format, e.g., `'1.1'`.                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| `INET`      | Requires supported <InternalLink path="inet">`INET`</InternalLink> string format, e.g, `'192.168.0.1'`.                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| `INT`       | Requires supported <InternalLink path="int">`INT`</InternalLink> string format, e.g., `'10'`.                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `INTERVAL`  | Requires supported <InternalLink path="interval">`INTERVAL`</InternalLink> string format, e.g., `'1h2m3s4ms5us6ns'`.                                                                                                                                                                                                                                                                                                                                                                                                                               |
| `TIME`      | Requires supported <InternalLink path="time">`TIME`</InternalLink> string format, e.g., `'01:22:12'` (microsecond precision).                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `TIMESTAMP` | Requires supported <InternalLink path="timestamp">`TIMESTAMP`</InternalLink> string format, e.g., `'2016-01-25 10:10:10.555555'`.                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `TSQUERY`   | Requires supported <InternalLink path="tsquery">`TSQUERY`</InternalLink> string format, e.g., `'Requires & supported & TSQUERY & string & format'`. Note that casting a string to a `TSQUERY` will not normalize the tokens into lexemes. To do so, [use `to\_tsquery()`, `plainto\_tsquery()`, or `phraseto\_tsquery()`](#convert-string-to-tsquery).                                                                                                                                                                                             |
| `TSVECTOR`  | Requires supported <InternalLink path="tsvector">`TSVECTOR`</InternalLink> string format, e.g., `'Requires supported TSVECTOR string format.'`. Note that casting a string to a `TSVECTOR` will not normalize the tokens into lexemes. To do so, [use `to\_tsvector()`](#convert-string-to-tsvector).                                                                                                                                                                                                                                              |
| `UUID`      | Requires supported <InternalLink path="uuid">`UUID`</InternalLink> string format, e.g., `'63616665-6630-3064-6465-616462656562'`.                                                                                                                                                                                                                                                                                                                                                                                                                  |

### `STRING` vs. `BYTES`

While both `STRING` and `BYTES` can appear to have similar behavior in many situations, one should understand their nuance before casting one into the other.

`STRING` treats all of its data as characters, or more specifically, Unicode code points. `BYTES` treats all of its data as a byte string. This difference in implementation can lead to dramatically different behavior. For example, let's take a complex Unicode character such as ☃ ([the snowman emoji](https://emojipedia.org/snowman)):

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT length('☃'::string);
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  length
+--------+
       1
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT length('☃'::bytes);
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  length
+--------+
       3
```

In this case, <InternalLink path="functions-and-operators#string-and-byte-functions">`LENGTH(string)`</InternalLink> measures the number of Unicode code points present in the string, whereas <InternalLink path="functions-and-operators#string-and-byte-functions">`LENGTH(bytes)`</InternalLink> measures the number of bytes required to store that value. Each character (or Unicode code point) can be encoded using multiple bytes, hence the difference in output between the two.

#### Translate literals to `STRING` vs. `BYTES`

A literal entered through a SQL client will be translated into a different value based on the type:

* `BYTES` gives a special meaning to the pair `\x` at the beginning, and translates the rest by substituting pairs of hexadecimal digits to a single byte. For example, `\xff` is equivalent to a single byte with the value of 255. For more information, see <InternalLink path="sql-constants#string-literals-with-character-escapes">SQL Constants: String literals with character escapes</InternalLink>.
* `STRING` does not give a special meaning to `\x`, so all characters are treated as distinct Unicode code points. For example, `\xff` is treated as a `STRING` with length 4 ( `\`, `x`, `f`, and `f` ).

### Cast hexadecimal digits to `BIT`

You can cast a `STRING` value of hexadecimal digits prefixed by `x` or `X` to a `BIT` value.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT 'XAB'::BIT(8)
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
    bit
------------
  10101011
(1 row)
```

### Concatenate `STRING` values with values of other types

`STRING` values can be concatenated with any non-`ARRAY`, non-`NULL` type, resulting in a `STRING` value.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT 1 || 'item';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ?column?
------------
  1item
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT true || 'item';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ?column?
------------
  titem
(1 row)
```

Concatenating a `STRING` value with a <InternalLink path="null-handling">`NULL` value</InternalLink> results in a `NULL` value.

For example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT NULL || 'item';
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  ?column?
------------
  NULL
(1 row)
```

### Convert `STRING` to `TIMESTAMP`

You can use the <InternalLink path="functions-and-operators">`parse_timestamp()` function</InternalLink> to parse strings in `TIMESTAMP` format.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT parse_timestamp ('2022-05-28T10:53:25.160Z');
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
      parse_timestamp
--------------------------
2022-05-28 10:53:25.16
(1 row)
```

### Convert `STRING` to `TSVECTOR`

You can use the <InternalLink path="functions-and-operators#full-text-search-functions">`to_tsvector()` function</InternalLink> to parse strings in <InternalLink path="tsvector">`TSVECTOR`</InternalLink> format. This will normalize the tokens into lexemes, and will add an integer position to each lexeme.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT to_tsvector('How do trees get on the internet?');
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
           to_tsvector
---------------------------------
  'get':4 'internet':7 'tree':3
```

For more information on usage, see <InternalLink path="full-text-search">Full-Text Search</InternalLink>.

### Convert `STRING` to `TSQUERY`

You can use the <InternalLink path="functions-and-operators#full-text-search-functions">`to_tsquery()`, `plainto_tsquery()`, and `phraseto_tsquery()` functions</InternalLink> to parse strings in <InternalLink path="tsquery">`TSQUERY`</InternalLink> format. This will normalize the tokens into lexemes.

When using `to_tsquery()`, the string input must be formatted as a <InternalLink path="tsquery#syntax">`TSQUERY`</InternalLink>, with operators separating tokens.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SELECT to_tsquery('How & do & trees & get & on & the & internet?');
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
          to_tsquery
-------------------------------
  'tree' & 'get' & 'internet'
```

For more information on usage, see <InternalLink path="full-text-search">Full-Text Search</InternalLink>.

## See also

* <InternalLink path="data-types">Data Types</InternalLink>
* <InternalLink path="sql-constants#string-literals">String literal syntax</InternalLink>