---
title: "String Functions"
description: "List of string functions in GSQL."
component: "gsql-ref"
version: "4.3"
module: "querying"
html_url: "/gsql-ref/4.3/querying/func/string-functions"
canonical_url: "https://www.tigergraph.com/docs/gsql-ref/4.3/querying/func/string-functions"
markdown_url: "https://www.tigergraph.com/docs/gsql-ref/4.3/querying/func/string-functions.md"
---

[View as HTML](https://www.tigergraph.com/docs/gsql-ref/4.3/querying/func/string-functions)

# String Functions

This page lists the string functions available in the GSQL query language. Examples of string functions are converting to uppercase or lowercase, padding or trimming characters, extracting substrings and comparing phonetic values for English words.

For type conversion functions, such as converting a `STRING` to an `int` or `DATETIME`, please see the [Type Conversion Functions](https://www.tigergraph.com/docs/gsql-ref/4.3/querying/func/type-conversion-functions.md) page.

## ascii()

### Syntax

`ascii( str )`

### Description

Returns the ASCII (numeric) position of the first character in a string. If the argument is an empty string, returns 0.

### Return type

`INT`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | A string value | `STRING` |

### Example

```text
ascii("") => 0
ascii("A") => 65
```

## chr()

### Syntax

`chr( n )`

### Description

Converts an integer to a character according to its ASCII position. If the input value `n` is greater than 255, returns the character at the position of the modulo of `n / 256`.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `n` | An integer value | `INT` |

### Examples

```text
chr(65) => 'A'
chr(322) => 'B'
```

## difference()

### Syntax

`difference(str1, str2)`

### Description

Compares the [Soundex codes](https://www.tigergraph.com/docs/gsql-ref/4.3/querying/func/string-functions.md#_soundex) of two strings and returns an integer. The return value ranges from 0 to 4, and it indicates the similarity between the input strings' phonetic representation values.

0 indicates weak similarity, and 4 indicates strong similarity with identical phonetic representation values.

### Return type

`INT`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str1` | A string value | `STRING` |
| `str2` | A string value | `STRING` |

### Examples

```text
difference("Johnson", "Jonson") => 4
difference("Adams", "Addamms") => 4
difference("Mary", "Bob") => 2
```

## find\_in\_set()

### Syntax

`find_in_set(str, str_list)`

### Description

Returns the position of a string within a list of strings separated by commas.

If `string` is not found in `string_list`, this function returns -1.

> [!NOTE]
> `find_in_set` does _not_ ignore whitespace after a comma. For example, `find_in_set("a", "b, a, a") = -1`, indicating that `a` is not an element in the list. This happens because the second and third elements of the list are `space+a`, rather than `a`.

### Return type

`INT`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | A string value | `STRING` |
| `str_list` | A string representation of a list of strings. | `STRING` |

### Example

```text
find_in_set("a","b,c,d") => -1
find_in_set("a","a,b,c") => 0
find_in_set("a","b,a,d") => 1
```

## gsql\_uuid\_v4()

### Syntax

`gsql_uuid_v4()`

### Description

Generates and returns a [version-4 universally unique identifier (UUID)](https://en.wikipedia.org/wiki/Universally%5Funique%5Fidentifier#Version%5F4%5F%28random%29).

### Return type

`STRING`

### Parameters

None.

## insert()

### Syntax

`insert(str1, position[, number], str2)`

### Description

Inserts a string within a string at the specified position and for a certain number of characters, and replaces a specified number of characters starting from the insertion position. The starting index is 0.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str1` | The string to insert another string into | `STRING` |
| `position` | The index of the starting position to insert the string | `INT` |
| `number` | Optional. The number of characters from the original string that will be replaced. If the argument is left off, it defaults to 0. | `STRING` |
| `str2` | The string to be inserted | `STRING` |

### Examples

```text
insert("tigergraph.com", 0, 10, "Example") => "Example.com”
insert("tigergraph.com", 0, 2, "Example") => "Examplegergraph.com”
insert("tigergraph.com", 2, 20, "Example") => ”tiExample”
insert("Complete blank.", 9, "every ") => "Complete every blank."
```

## instr()

### Syntax

`instr (str, substr [, position, occurrence])`

### Description

Searches a string `str` for a substring `substr` and returns the location of the substring in the string. If a substring that is equal to `substr` is found, then the function returns an integer indicating the position of the first character of this substring. If no such substring is found, then the function returns -1.

### Return type

`INT`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | The string to search | `STRING` |
| `substr` | The string to search for in `str` | `STRING` |
| `position` | Optional. The position is a nonzero integer indicating the character of `str` from where the search begins. If omitted, it defaults to 0\. The first position in the string is 0\. If `position` is negative, then the function counts backward from the end of `str` and then searches backward from the resulting position. | `STRING` |
| `occurrence` | Optional, The occurrence is an integer indicating which occurrence of `substr` in `str` the function should search for. | `STRING` |

### Example

```text
instr("This is the thing", "Th") -> 0;
instr("This is the thing", "is", 3) -> 5;
```

## left()

### Syntax

`left(str, number_of_chars)`

### Description

Extracts a number of characters from a string starting from position 0 (capturing left to right).

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | A string value | `STRING` |
| `number_of_chars` | The number of characters to extract | `INT` |

## length()

### Syntax

`length(str)`

### Description

Returns the length of the input string.

### Return type

`INT`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | The string whose length to evaluate | `STRING` |

### Example

```text
length("hello world") -> 11
length("") -> 0
```

## ltrim()

### Syntax

`ltrim( str[, set] )`

### Description

Removes all occurrences of the characters contained in a set from a string from the left side.

The function begins scanning the string from its first character, removing all characters that appear in `set` until reaching a character not in `set`, then returning the result.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | A string value | `STRING` |
| `set` | Optional. A string of characters. The distinct characters from the string form the set. If not specified, it defaults to a single space. | `STRING` |

## lower()

### Syntax

`lower(str)`

### Description

Returns the input string with all letters in lowercase.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | The string to convert to lowercase | `STRING` |

### Example

```text
lower("GSQL") -> "gsql"
```

## lpad()

### Syntax

`lpad(str, padded_length [, pad_str] )`

### Description

Pads the left side of a string with another pad string. If the pad string `pad_str` is omitted, it will pad with white space. If the parameter length is smaller than the original string, it will truncate the string from the right side.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | The string to pad characters to | `STRING` |
| `padded_length` | The number of characters to return. If the `padded_length` is smaller than the original string, the `lpad` function will truncate the string to the size of `padded_length`. | `INT` |
| `pad_str` | Optional. This is the string that will be padded to the left-hand side of `str`. If this parameter is omitted, the `lpad` function will pad spaces to the left side of `str`. | `STRING` |

### Example

```text
 lpad("PQR", 5) -> "  PQR"
 lpad("PQR", 2) -> "PQ"
 lpad("PQR", 10, "ABC") -> "ABCABCAPQR"
```

## repeat()

### Syntax

`repeat(str, repetitions)`

### Description

Repeats a string the given number of times, with no intervening space.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | The original string | `STRING` |
| `repetitions` | The number of times to include `str` in the output. | `INT` |

### Example

```text
 repeat("Abcd", 2) -> "AbcdAbcd"
```

## replace()

### Syntax

`replace(str, str_to_replace [, replacement_str])`

### Description

Replaces a sequence of characters in a string with another set of characters.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | The original string whose substrings are to be replaced | `STRING` |
| `str_to_replace` | The string that will be searched for and replaced in `str` | `STRING` |
| `replacement_str` | Optional. The string that will replace `str_to_replace`. If omitted, `replace()` removes all occurrences of `string_to_replace` and returns the resulting string. | `STRING` |

### Examples

```text
 replace("SSQLL", "S", "G") -> "GGQLL"
 replace("SSQLL", "SQL", "Q") -> "SQL"
 replace("SSQLL", "L") -> "SSQ"
```

## reverse()

### Syntax

`reverse(str)`

### Description

Returns a string with its characters in reverse order.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | The original string | `STRING` |

### Examples

```text
 reverse("0246813579") -> "9753186420"
 reverse("Never odd or even") -> "neve ro ddo reveN"
```

## right()

### Syntax

`right(str, number_of_chars)`

### Description

Extracts a number of characters from a string starting from the right.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | A string value | `STRING` |
| `number_of_chars` | The number of characters to extract | `INT` |

## rpad()

### Syntax

`rpad(str, padded_length [, pad_str] )`

### Description

Pads the right side of a string (`str`) with another pad string. If the pad string (`pad_str`) is omitted, it will pad with white space. If the parameter length is smaller than the original string, it will truncate the string from the right side.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | The string to pad characters to | `STRING` |
| `padded_length` | The number of characters to return. If the `padded_length` is smaller than the original string, the `lpad` function will truncate the string to the size of `padded_length`. | `INT` |
| `pad_str` | Optional. This is the string that will be padded to the right-hand side of `str`. If this parameter is omitted, the `lpad` function will pad spaces to the right side of `str`. | `STRING` |

### Example

```text
rpad("PQR", 5) -> "PQF  "
lpad("PQR", 2) -> "PQ"
lpad("PQR", 10, "ABC") -> "ABCABCAPQR"
```

## rtrim()

### Syntax

`rtrim( str [,set] )`

### Description

Removes all occurrences of the characters contained in a set from a string from the right side.

The function begins scanning the string from its last character and removes all characters that appear in `set` until reaching a character not in `set` and then returns the result.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | A string value | `STRING` |
| `set` | Optional. A string of characters. The distinct characters from the string form the set. If not specified, it defaults to a single space. | `STRING` |

## soundex()

### Syntax

`soundex( str )`

### Description

Returns a character string containing the [Soundex](https://en.wikipedia.org/wiki/Soundex) code of `str`. This function lets you compare words that are spelled differently, but sound alike in English.

Soundex is a phonetic algorithm defined in _The Art of Computer Programming_, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:

1. Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
2. Assign numbers to the remaining letters (after the first) as follows:  
```text  
b, f, p, v = 1  
c, g, j, k, q, s, x, z = 2  
d, t = 3  
l = 4  
m, n = 5  
r = 6  
```
3. If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then retain the first letter and omit the rest of all the adjacent letters with the same number.
4. Return the first four bytes padded with 0.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | A string value | `STRING` |

### Examples

```text
soundex("Ashcraft") => "A261"
soundex("Burroughs") => "B620"
soundex("Burrows") => "B620"
```

## space()

### Syntax

`space( n )`

### Description

Returns a string that contains the specified number of space characters

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `n` | An integer value | `INT` |

### Examples

```text
space(0) = ””
space(1) = ” ”
space(5) = ”     ”
```

## substr()

### Syntax

`substr(str, start [, length])`

### Description

Returns the substring indicated by the start point and length. If the parameter length is omitted, then the returned substring will extend to the end of the given input string.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | The string to extract substring from | `STRING` |
| `start` | The position that indicates the start of the substring | `INT` |
| `length` | Optional. The length of the substring. If omitted, the substring will extend from `start` to the end of `str`. | `INT` |

### Example

```text
substr("ABCDE", 2) -> "CDE"
substr("ABCDE", 2, 2) -> "CD"
substr("ABCDE", -2, 1) -> "D"
```

## translate()

### Syntax

`translate( str_origin, characters, translations )`

### Description

Returns the string from the first argument after the characters specified in the second argument are translated into the characters specified at the same index in the third argument.

The function will return an error if `characters` and `translations` have different lengths.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str_origin` | A string value | `STRING` |
| `characters` | A string of characters | `STRING` |
| `translations` | A string of characters | `STRING` |

### Examples

```text
translate(”Hello world”, "", "") => ”Hello world”
translate(”Hello world”, "o", "U") => ”HellU wUrld”
translate(”Hello world”, "lo", "aU") => ”HeaaU wUrad”
translate(””, "lo", "aU") => ””
```

## trim()

### Syntax

`trim( [ [ LEADING | TRAILING | BOTH ] [removal_char FROM] ] str )`

### Description

Trims characters from the leading and/or trailing ends of a string.

By using one of the keywords `LEADING`, `TRAILING`, or `BOTH`, the user can specify that characters are to be removed from the left end, right end, or both ends of the string respectively. `BOTH` is the default and will be used if no keywords are specified.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `removal_char` | Optional. The character to remove. If `removal_char` is not specified, the function will remove whitespaces, including spaces, tabs, and newlines. If `removal_char` is specified, the user must also write the keyword `FROM` between `removal_char` and `str`. | `STRING` |
| `str` | A string value. | `STRING` |

### Example

```gsql
trim("  Abc   ") => "Abc"
trim( LEADING " a A   ") => "a A   "
trim( TRAILING "a" FROM "aa ABC aaa") => "aa ABC "
```

## upper()

### Syntax

`upper(str)`

### Description

Returns the input string with all letters in uppercase.

### Return type

`STRING`

### Parameters

| Parameter | Description | Data type |
| --- | --- | --- |
| `str` | The string to convert to uppercase | `STRING` |

### Example

```text
upper("gsql") -> "GSQL"
```
