# String Functions

#### <mark style="color:purple;">**| |**</mark>**&#x20;- Concatenation Operator**

returns the final string expression by concating two expressions

```sql
> select 'hello' || 'world';
'helloworld'

> select concat('hello ', 'world')
'hello world'
```

#### <mark style="color:purple;">LIKE</mark> - Like Operator

The `like` operator is used to match a specified pattern in a string expression. The pattern contains regular characters like %,  \_

```sql
select colA from table where colA like 'P_%'
```

#### <mark style="color:purple;">ILIKE</mark> - ILike Operator

The `ilike` is a case-insensitive operator used to match a specified pattern in a string expression. The pattern contains regular characters like %,  \_

```sql
> select colA from table where colA ilike 'P_%'
> select colA from table where colA not ilike 'P_%'
```

#### <mark style="color:purple;">RLIKE</mark> - RLike Operator

The `ilike` is a case-insensitive operator used to match a specified pattern in a string expression. The pattern contains regular characters like %,  \_

```sql
> select 'E6data' RLIKE 'E[0-9][a-z]+'
true
```

#### <mark style="color:purple;">CHARACTER\_LENGTH / CHAR\_LENGTH /  LEN / LENGTH (</mark>  \<expr>  <mark style="color:purple;">)</mark> &#x20;

Returns the length of a given string expression.

```sql
> select character_length('e6data');
6
```

#### <mark style="color:purple;">REPLACE(</mark> \<expr>,  \<search>  \[,  \<replace>  ]   <mark style="color:purple;">)</mark> &#x20;

`expr` - string expression to be searched/modified.\
`search` - string expression to be searched for in the input expression.\
`replace` - *optional* string expression to be replaced with the search expression. Default is an empty string.

Replaces all instances of `search` with `replace` in string expression

```sql
> select replace('hard worker', 'hard', 'smart');
'smart worker'

> select replace('hard worker', 'hard');
'worker'
```

#### <mark style="color:purple;">TRIM(</mark>  \<expr>  <mark style="color:purple;">)</mark>

Trims/removes leading and trailing whitespace in the input string expression.

```sql
> select trim('   Fastest database engine  ');
'Fastest database engine'
```

#### <mark style="color:purple;">LTRIM(</mark>  \<expr>  <mark style="color:purple;">)</mark>

Removes leading whitespace in the input string expression.

```sql
> select ltrim('  Fastest database engine--');
'Fastest database engine--'
```

#### <mark style="color:purple;">RTRIM(</mark>  \<expr>  <mark style="color:purple;">)</mark>

Removes trailing whitespace in the input string expression.

```sql
> select rtrim('Fastest database engine  ');
'Fastest database engine'
```

#### <mark style="color:purple;">LOWER(</mark>  \<expr>  <mark style="color:purple;">)</mark>

Returns the input string expression converted to lowercase characters.

```sql
> select lower('FASTEST DataBAse ENgine- 2022');
'fastest database engine- 2022'
```

#### <mark style="color:purple;">UPPER(</mark>  \<expr>  <mark style="color:purple;">)</mark> &#x20;

Returns the input string expression converted to uppercase characters.

```sql
> select upper('Fastest database engine- 2022');
'FASTEST DATABASE ENGINE- 2022'
```

#### <mark style="color:purple;">SUBSTRING(</mark>  \<expr>,  start,  length  <mark style="color:purple;">)</mark>&#x20;

`start` - numeric expression representing a starting position in the string.\
`length` - numeric expression representing the length of the substring.

Returns a substring from an input `string` expression of the given `length` beginning from the `start` index.

*Note: Indexing starts from 1.*

```sql
> select substring('Fastest database engine', 9, 8)
'database'
```

#### <mark style="color:purple;">SUBSTR(</mark> \<expr>,   start,  length  <mark style="color:purple;">)</mark>&#x20;

This is an alias of the [substring](#substring-less-than-expr-greater-than-start-length) function

#### <mark style="color:purple;">INITCAP(</mark>  \<expr>  <mark style="color:purple;">)</mark>

Returns string expression with the first letter of each word converted to uppercase.

<pre class="language-sql"><code class="lang-sql">> select initcap('hello world');
<strong>'Hello World'
</strong></code></pre>

#### <mark style="color:purple;">CHARINDEX(</mark>  \<expr>  , string  \[,  startindex]  <mark style="color:purple;">)</mark>

Returns the first occurrence of the starting position of the input expression within `string`. Default value of `startindex` is 1.

```sql
> select charindex('a', 'abc')
1

> select charindex('a', 'abc', 2)
0
```

#### <mark style="color:purple;">POSITION(</mark>  \<expr>  in \<expr> from \[startindex]  <mark style="color:purple;">)</mark>

Returns first occurrence of the starting position of the input expression within `string`. Default value of `startindex` is 1.

```sql
> select position('6' in 'e6data-e6data');
2

> select position('6' in 'e6data-e6data' from 3);
9
```

#### <mark style="color:purple;">RIGHT (</mark> \<string><mark style="color:purple;">,</mark> \<offset> <mark style="color:purple;">)</mark> <a href="#right-string-function" id="right-string-function"></a>

Returns the rightmost substring of its input. Offset index starts from 1.&#x20;

Supported datatypes are string, integer, decimal, date, timestamp

```sql
> SELECT right('abc', 2);
bc
```

#### <mark style="color:purple;">LEFT (</mark> \<string><mark style="color:purple;">,</mark> \<offset> <mark style="color:purple;">)</mark> <a href="#right-string-function" id="right-string-function"></a>

Returns the leftmost substring of its input. Offset index starts from 1

Supported datatypes are string, integer, decimal, date, timestamp

```sql
> SELECT left('abc', 2);
ab
```

#### <mark style="color:purple;">LOCATE (</mark> \<substring> <mark style="color:purple;">,</mark> \<string> *<mark style="color:purple;">\[,</mark> \<start\_position> <mark style="color:purple;">]</mark>* <mark style="color:purple;"></mark><mark style="color:purple;">)</mark> <a href="#locate-string-function" id="locate-string-function"></a>

Returns the position of the first occurrence of a substring in a string

Index starts with 1

```sql
> SELECT LOCATE('6', 'e6data');
2
```

```sql
> SELECT LOCATE('6', 'e6data', 3);
0
```

#### <mark style="color:purple;">CONTAINS\_SUBSTR(</mark> \<expr> , \<search\_value\_literal> <mark style="color:purple;">)</mark>

Returns TRUE if the value exists, otherwise returns FALSE.

Supported datatype are, string, integer, decimal, date, timestamp

<pre class="language-sql"><code class="lang-sql"><strong>> SELECT CONTAINS_SUBSTR('abcdef', 'ef');
</strong>TRUE
</code></pre>

**Limitations**

* Complex types are not supported.
* Unicode characters are not supported.

#### <mark style="color:purple;">INSTR(</mark> \<expr>, subvalue *\[, position, occurrence]* <mark style="color:purple;">)</mark>

Returns the lowest position of subvalue in value.

```sql
> SELECT INSTR('helloooooooo','oo', 1, 3)
9
```

**Limitations**

* Complex types are not supported.

#### <mark style="color:purple;">SOUNDEX(</mark> \<expr> <mark style="color:purple;">)</mark>

Returns a string that contains a phonetic representation of the input string/integer.

```sql
> select soundex('winter')
w536
```

#### <mark style="color:purple;">SPLIT(</mark> \<expr>, \[delimiter] <mark style="color:purple;">)</mark>

Splits `<expr>` using the `delimiter` argument.

The default delimiter is the comma `,`\
Supported Datatype are string

```sql
> SELECT SPLIT('A-B-C-D-E-F-G-H', '-' )	 
[ A, B, C, D, E, F, G, H ]
```

**Limitations**

* Even after converting to Varchar, the passing of a date or timestamp is not supported.

#### <mark style="color:purple;">SPLIT\_PART(</mark> \<expr>,  \<delimiter>, \<position> <mark style="color:purple;">)</mark>

Splits a given `<expr>`  based on the <`delimiter>`\
Supported Datatype are string

```sql
> SELECT SPLIT_PART('abc@def@ghi','@',2)
def
```

**Limitations**

* Unicode characters are not supported

#### <mark style="color:purple;">ASCII(</mark> \<expr> <mark style="color:purple;">)</mark>

Return the ASCII code for the first character of a given string.\
Supported Datatype are string

```sql
> select ascii('a')
97
```

#### <mark style="color:purple;">REPEAT(</mark> \<input\_string> , \<repeat\_value> <mark style="color:purple;">)</mark>

Create a string by duplicating the input by number of repetitions specified.

Supported datatypes: string, integer, decimal

```sql
> select repeat('a', 4)
aaaa
```

Usages:

* Output is NULL, if either \<input\_string> or \<repeat\_value> is NULL
* Unicodes are not supported in \<input\_string>

<mark style="color:purple;">**ENDSWITH / ENDS\_WITH(**</mark>**&#x20;\<expr\_1> , \<expr\_2>&#x20;**<mark style="color:purple;">**)**</mark>

Returns TRUE if the first expression ends with second expression

Support Datatype is String

```sql
> SELECT ENDSWITH('e6data','ta');
true
```

<mark style="color:purple;">**STARTSWITH / STARTS\_WITH(**</mark>**&#x20;\<expr\_1> , \<expr\_2>&#x20;**<mark style="color:purple;">**)**</mark>

Returns TRUE if the first expression starts with second expression

Support Datatype is String

```sql
> SELECT STARTS_WITH('e6data','ta');
false
```

#### <mark style="color:purple;">STRPOS (</mark> \<string>,  \<substring>  *<mark style="color:purple;">\[,</mark> \<start\_position> <mark style="color:purple;">]</mark>* <mark style="color:purple;"></mark><mark style="color:purple;">)</mark> <a href="#locate-string-function" id="locate-string-function"></a>

Returns the position of the first occurrence of a substring in a string

Index starts with 1

```sql
> SELECT STRPOS('e6data','6');
2
```

```sql
> SELECT STRPOS('e6data','6', 3);
0
```

#### <mark style="color:purple;">LPAD (</mark> \<expr>,  \<length>  *<mark style="color:purple;">\[,</mark> \<pad> <mark style="color:purple;">]</mark>* <mark style="color:purple;"></mark><mark style="color:purple;">)</mark> <a href="#locate-string-function" id="locate-string-function"></a>

`LPAD` function is used to left-pad a string with a specified character or set of characters to a certain length

```sql
> SELECT LPAD('123', 5, '0');
00123
```

#### <mark style="color:purple;">RPAD (</mark> \<expr>,  \<length>  *<mark style="color:purple;">\[,</mark> \<pad> <mark style="color:purple;">]</mark>* <mark style="color:purple;"></mark><mark style="color:purple;">)</mark> <a href="#locate-string-function" id="locate-string-function"></a>

`RPAD` function is used to right-pad a string with a specified character or set of characters to a certain length

```sql
> SELECT RPAD('123', 5, '0');
12300
```

#### <mark style="color:purple;">REVERSE (</mark> \<expr> <mark style="color:purple;">)</mark> <a href="#locate-string-function" id="locate-string-function"></a>

Returns the reversed order of characters of string, integer and boolean values

Support datatype: INT/DOUBLE/VARCHAR/BOOLEAN

```sql
> select reverse(53);
35
```

<mark style="color:purple;">**TO\_CHAR**</mark>/<mark style="color:purple;">**TO\_VARCHAR**</mark> ( \<expr> )&#x20;

<mark style="color:purple;">**TO\_CHAR**</mark>/<mark style="color:purple;">**TO\_VARCHAR**</mark> ( \<date\_or\_timestamp\_expr> \[, '\<format>' ] )

This function transforms the given expression into a string.

Support datatype: INT/FLOAT/DOUBLE/STRING/DATE/TIMESTAMP/BOOL

<pre class="language-sql"><code class="lang-sql"><strong>> select to_char(cast('2022-01-02 18:09:22' as timestamp), 'dd MMM yyyy hh:mm:ss a');
</strong>02 Jan 2022 06:09:22 PM
</code></pre>

**Usages**:

1. we support java [format string](https://docs.oracle.com/javase/8/docs/api/java/time/format/DateTimeFormatter.html#ISO_DATE) for date and timestamp type. However we are not supporting zone as the format
2. In the formate string we don’t support `#` as it is a reserved keyword.
3. We don’t support format parameter for other data type except for date and timestamp type


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.e6data.com/product-documentation/sql-command-reference/string-functions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.