# 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