# String functions
String functions process and manipulate character strings or expressions that evaluate to character strings. When the *string* argument in these functions is a literal value, it must be enclosed in single quotation marks. Supported data types include CHAR and VARCHAR.
The following section provides the function names, syntax, and descriptions for supported functions. All offsets into strings are one-based.
**Topics**
+ [\$1\$1 (Concatenation) operator](concat_op.md)
+ [BTRIM function](BTRIM.md)
+ [CONCAT function](CONCAT.md)
+ [FORMAT\$1STRING function](FORMAT_STRING.md)
+ [LEFT and RIGHT functions](LEFT.md)
+ [LENGTH function](LENGTH.md)
+ [LOWER function](LOWER.md)
+ [LPAD and RPAD functions](LPAD.md)
+ [LTRIM function](LTRIM.md)
+ [POSITION function](POSITION.md)
+ [REGEXP\$1COUNT function](REGEXP_COUNT.md)
+ [REGEXP\$1INSTR function](REGEXP_INSTR.md)
+ [REGEXP\$1REPLACE function](REGEXP_REPLACE.md)
+ [REGEXP\$1SUBSTR function](REGEXP_SUBSTR.md)
+ [REPEAT function](REPEAT.md)
+ [REPLACE function](REPLACE.md)
+ [REVERSE function](REVERSE.md)
+ [RTRIM function](RTRIM.md)
+ [SPLIT function](split.md)
+ [SPLIT\$1PART function](SPLIT_PART.md)
+ [SUBSTRING function](SUBSTRING.md)
+ [TRANSLATE function](TRANSLATE.md)
+ [TRIM function](TRIM.md)
+ [UPPER function](UPPER.md)
+ [UUID function](UUID.md)
# \$1\$1 (Concatenation) operator
Concatenates two expressions on either side of the \$1\$1 symbol and returns the concatenated expression.
The concatentation operator is similar to [CONCAT function](CONCAT.md).
**Note**
For both the CONCAT function and the concatenation operator, if one or both expressions is null, the result of the concatenation is null.
## Syntax
```
expression1 || expression2
```
## Arguments
*expression1*, *expression2*
Both arguments can be fixed-length or variable-length character strings or expressions.
## Return type
The \$1\$1 operator returns a string. The type of string is the same as the input arguments.
## Example
The following example concatenates the FIRSTNAME and LASTNAME fields from the USERS table:
```
select firstname || ' ' || lastname
from users
order by 1
limit 10;
concat
-----------------
Aaron Banks
Aaron Booth
Aaron Browning
Aaron Burnett
Aaron Casey
Aaron Cash
Aaron Castro
Aaron Dickerson
Aaron Dixon
Aaron Dotson
(10 rows)
```
To concatenate columns that might contain nulls, use the [NVL and COALESCE functions](NVL_function.md) expression. The following example uses NVL to return a 0 whenever NULL is encountered.
```
select venuename || ' seats ' || nvl(venueseats, 0)
from venue where venuestate = 'NV' or venuestate = 'NC'
order by 1
limit 10;
seating
-----------------------------------
Ballys Hotel seats 0
Bank of America Stadium seats 73298
Bellagio Hotel seats 0
Caesars Palace seats 0
Harrahs Hotel seats 0
Hilton Hotel seats 0
Luxor Hotel seats 0
Mandalay Bay Hotel seats 0
Mirage Hotel seats 0
New York New York seats 0
```
# BTRIM function
The BTRIM function trims a string by removing leading and trailing blanks or by removing leading and trailing characters that match an optional specified string.
## Syntax
```
BTRIM(string [, trim_chars ] )
```
## Arguments
*string*
The input VARCHAR string to be trimmed.
*trim\$1chars*
The VARCHAR string containing the characters to be matched.
## Return type
The BTRIM function returns a VARCHAR string.
## Examples
The following example trims leading and trailing blanks from the string `' abc '`:
```
select ' abc ' as untrim, btrim(' abc ') as trim;
untrim | trim
----------+------
abc | abc
```
The following example removes the leading and trailing `'xyz'` strings from the string `'xyzaxyzbxyzcxyz'`. The leading and trailing occurrences of `'xyz'` are removed, but occurrences that are internal within the string are not removed.
```
select 'xyzaxyzbxyzcxyz' as untrim,
btrim('xyzaxyzbxyzcxyz', 'xyz') as trim;
untrim | trim
-----------------+-----------
xyzaxyzbxyzcxyz | axyzbxyzc
```
The following example removes the leading and trailing parts from the string `'setuphistorycassettes'` that match any of the characters in the *trim\$1chars* list `'tes'`. Any `t`, `e`, or `s` that occur before another character that is not in the *trim\$1chars* list at the beginning or ending of the input string are removed.
```
SELECT btrim('setuphistorycassettes', 'tes');
btrim
-----------------
uphistoryca
```
# CONCAT function
The CONCAT function concatenates two expressions and returns the resulting expression. To concatenate more than two expressions, use nested CONCAT functions. The concatenation operator (`||`) between two expressions produces the same results as the CONCAT function.
**Note**
For both the CONCAT function and the concatenation operator, if one or both expressions is null, the result of the concatenation is null.
## Syntax
```
CONCAT ( expression1, expression2 )
```
## Arguments
*expression1*, *expression2*
Both arguments can be a fixed-length character string, a variable-length character string, a binary expression, or an expression that evaluates to one of these inputs.
## Return type
CONCAT returns an expression. The data type of the expression is the same type as the input arguments.
If the input expressions are of different types, AWS Clean Rooms tries to implicitly type casts one of the expressions. If values can't be cast, an error is returned.
## Examples
The following example concatenates two character literals:
```
select concat('December 25, ', '2008');
concat
-------------------
December 25, 2008
(1 row)
```
The following query, using the `||` operator instead of CONCAT, produces the same result:
```
select 'December 25, '||'2008';
concat
-------------------
December 25, 2008
(1 row)
```
The following example uses two CONCAT functions to concatenate three character strings:
```
select concat('Thursday, ', concat('December 25, ', '2008'));
concat
-----------------------------
Thursday, December 25, 2008
(1 row)
```
To concatenate columns that might contain nulls, use the [NVL and COALESCE functions](NVL_function.md). The following example uses NVL to return a 0 whenever NULL is encountered.
```
select concat(venuename, concat(' seats ', nvl(venueseats, 0))) as seating
from venue where venuestate = 'NV' or venuestate = 'NC'
order by 1
limit 5;
seating
-----------------------------------
Ballys Hotel seats 0
Bank of America Stadium seats 73298
Bellagio Hotel seats 0
Caesars Palace seats 0
Harrahs Hotel seats 0
(5 rows)
```
The following query concatenates CITY and STATE values from the VENUE table:
```
select concat(venuecity, venuestate)
from venue
where venueseats > 75000
order by venueseats;
concat
-------------------
DenverCO
Kansas CityMO
East RutherfordNJ
LandoverMD
(4 rows)
```
The following query uses nested CONCAT functions. The query concatenates CITY and STATE values from the VENUE table but delimits the resulting string with a comma and a space:
```
select concat(concat(venuecity,', '),venuestate)
from venue
where venueseats > 75000
order by venueseats;
concat
---------------------
Denver, CO
Kansas City, MO
East Rutherford, NJ
Landover, MD
(4 rows)
```
# FORMAT\$1STRING function
The FORMAT\$1STRING function creates a formatted string by substituting placeholders in a template string with the provided arguments. It returns a formatted string from printf-style format strings.
The FORMAT\$1STRING function works by replacing the placeholders in the template string with the corresponding values passed as arguments. This type of string formatting can be useful when you need to dynamically construct strings that include a mix of static text and dynamic data, such as when generating output messages, reports, or other types of informative text. The FORMAT\$1STRING function provides a concise and readable way to create these types of formatted strings, making it easier to maintain and update the code that generates the output.
## Syntax
```
format_string(strfmt, obj, ...)
```
## Arguments
*strfmt*
A STRING expression.
*obj*
A STRING or numeric expression.
## Return type
FORMAT\$1STRING returns a STRING.
## Example
The following example contains a template string that contains two placeholders: `%d` for a decimal (integer) value, and `%s` for a string value. The `%d` placeholder is replaced with the decimal (integer) value (`100`), and the %s placeholder is replaced with the string value (`"days"`). The output is a template string with the placeholders replaced by the provided arguments: `"Hello World 100 days"`.
```
SELECT format_string("Hello World %d %s", 100, "days");
Hello World 100 days
```
# LEFT and RIGHT functions
These functions return the specified number of leftmost or rightmost characters from a character string.
The number is based on the number of characters, not bytes, so that multibyte characters are counted as single characters.
## Syntax
```
LEFT ( string, integer )
RIGHT ( string, integer )
```
## Arguments
*string*
Any character string or any expression that evaluates to a character string.
*integer*
A positive integer.
## Return type
LEFT and RIGHT return a VARCHAR string.
## Example
The following example returns the leftmost 5 and rightmost 5 characters from event names that have IDs between 1000 and 1005:
```
select eventid, eventname,
left(eventname,5) as left_5,
right(eventname,5) as right_5
from event
where eventid between 1000 and 1005
order by 1;
eventid | eventname | left_5 | right_5
--------+----------------+--------+---------
1000 | Gypsy | Gypsy | Gypsy
1001 | Chicago | Chica | icago
1002 | The King and I | The K | and I
1003 | Pal Joey | Pal J | Joey
1004 | Grease | Greas | rease
1005 | Chicago | Chica | icago
(6 rows)
```
# LENGTH function
# LOWER function
Converts a string to lowercase. LOWER supports UTF-8 multibyte characters, up to a maximum of four bytes per character.
## Syntax
```
LOWER(string)
```
## Argument
*string*
The input parameter is a VARCHAR string (or any other data type, such as CHAR, that can be implicitly converted to VARCHAR).
## Return type
The LOWER function returns a character string that is the same data type as the input string.
## Examples
The following example converts the CATNAME field to lowercase:
```
select catname, lower(catname) from category order by 1,2;
catname | lower
----------+-----------
Classical | classical
Jazz | jazz
MLB | mlb
MLS | mls
Musicals | musicals
NBA | nba
NFL | nfl
NHL | nhl
Opera | opera
Plays | plays
Pop | pop
(11 rows)
```
# LPAD and RPAD functions
These functions prepend or append characters to a string, based on a specified length.
## Syntax
```
LPAD (string1, length, [ string2 ])
```
```
RPAD (string1, length, [ string2 ])
```
## Arguments
*string1*
A character string or an expression that evaluates to a character string, such as the name of a character column.
*length*
An integer that defines the length of the result of the function. The length of a string is based on the number of characters, not bytes, so that multi-byte characters are counted as single characters. If *string1* is longer than the specified length, it is truncated (on the right). If *length* is a negative number, the result of the function is an empty string.
*string2*
One or more characters that are prepended or appended to *string1*. This argument is optional; if it is not specified, spaces are used.
## Return type
These functions return a VARCHAR data type.
## Examples
Truncate a specified set of event names to 20 characters and prepend the shorter names with spaces:
```
select lpad(eventname,20) from event
where eventid between 1 and 5 order by 1;
lpad
--------------------
Salome
Il Trovatore
Boris Godunov
Gotterdammerung
La Cenerentola (Cind
(5 rows)
```
Truncate the same set of event names to 20 characters but append the shorter names with `0123456789`.
```
select rpad(eventname,20,'0123456789') from event
where eventid between 1 and 5 order by 1;
rpad
--------------------
Boris Godunov0123456
Gotterdammerung01234
Il Trovatore01234567
La Cenerentola (Cind
Salome01234567890123
(5 rows)
```
# LTRIM function
Trims characters from the beginning of a string. Removes the longest string containing only characters in the trim characters list. Trimming is complete when a trim character doesn't appear in the input string.
## Syntax
```
LTRIM( string [, trim_chars] )
```
## Arguments
*string*
A string column, expression, or string literal to be trimmed.
*trim\$1chars*
A string column, expression, or string literal that represents the characters to be trimmed from the beginning of *string*. If not specified, a space is used as the trim character.
## Return type
The LTRIM function returns a character string that is the same data type as the input *string* (CHAR or VARCHAR).
## Examples
The following example trims the year from the `listime` column. The trim characters in string literal `'2008-'` indicate the characters to be trimmed from the left. If you use the trim characters `'028-'`, you achieve the same result.
```
select listid, listtime, ltrim(listtime, '2008-')
from listing
order by 1, 2, 3
limit 10;
listid | listtime | ltrim
-------+---------------------+----------------
1 | 2008-01-24 06:43:29 | 1-24 06:43:29
2 | 2008-03-05 12:25:29 | 3-05 12:25:29
3 | 2008-11-01 07:35:33 | 11-01 07:35:33
4 | 2008-05-24 01:18:37 | 5-24 01:18:37
5 | 2008-05-17 02:29:11 | 5-17 02:29:11
6 | 2008-08-15 02:08:13 | 15 02:08:13
7 | 2008-11-15 09:38:15 | 11-15 09:38:15
8 | 2008-11-09 05:07:30 | 11-09 05:07:30
9 | 2008-09-09 08:03:36 | 9-09 08:03:36
10 | 2008-06-17 09:44:54 | 6-17 09:44:54
```
LTRIM removes any of the characters in *trim\$1chars* when they appear at the beginning of *string*. The following example trims the characters 'C', 'D', and 'G' when they appear at the beginning of VENUENAME, which is a VARCHAR column.
```
select venueid, venuename, ltrim(venuename, 'CDG')
from venue
where venuename like '%Park'
order by 2
limit 7;
venueid | venuename | btrim
--------+----------------------------+--------------------------
121 | ATT Park | ATT Park
109 | Citizens Bank Park | itizens Bank Park
102 | Comerica Park | omerica Park
9 | Dick's Sporting Goods Park | ick's Sporting Goods Park
97 | Fenway Park | Fenway Park
112 | Great American Ball Park | reat American Ball Park
114 | Miller Park | Miller Park
```
The following example uses the trim character `2` which is retrieved from the `venueid` column.
```
select ltrim('2008-01-24 06:43:29', venueid)
from venue where venueid=2;
ltrim
------------------
008-01-24 06:43:29
```
The following example does not trim any characters because a `2` is found before the `'0'` trim character.
```
select ltrim('2008-01-24 06:43:29', '0');
ltrim
-------------------
2008-01-24 06:43:29
```
The following example uses the default space trim character and trims the two spaces from the beginning of the string.
```
select ltrim(' 2008-01-24 06:43:29');
ltrim
-------------------
2008-01-24 06:43:29
```
# POSITION function
Returns the location of the specified substring within a string.
## Syntax
```
POSITION(substring IN string )
```
## Arguments
*substring*
The substring to search for within the *string*.
*string*
The string or column to be searched.
## Return type
The POSITION function returns an integer corresponding to the position of the substring (one-based, not zero-based). The position is based on the number of characters, not bytes, so that multi-byte characters are counted as single characters.
## Usage notes
POSITION returns 0 if the substring is not found within the string:
```
select position('dog' in 'fish');
position
----------
0
(1 row)
```
## Examples
The following example shows the position of the string `fish` within the word `dogfish`:
```
select position('fish' in 'dogfish');
position
----------
4
(1 row)
```
The following example returns the number of sales transactions with a COMMISSION over 999.00 from the SALES table:
```
select distinct position('.' in commission), count (position('.' in commission))
from sales where position('.' in commission) > 4 group by position('.' in commission)
order by 1,2;
position | count
---------+-------
5 | 629
(1 row)
```
# REGEXP\$1COUNT function
Searches a string for a regular expression pattern and returns an integer that indicates the number of times the pattern occurs in the string. If no match is found, then the function returns 0.
## Syntax
```
REGEXP_COUNT ( source_string, pattern [, position [, parameters ] ] )
```
## Arguments
*source\$1string*
A string expression, such as a column name, to be searched.
*pattern*
A string literal that represents a regular expression pattern.
*position*
A positive integer that indicates the position within *source\$1string* to begin searching. The position is based on the number of characters, not bytes, so that multibyte characters are counted as single characters. The default is 1. If *position* is less than 1, the search begins at the first character of *source\$1string*. If *position* is greater than the number of characters in *source\$1string*, the result is 0.
*parameters*
One or more string literals that indicate how the function matches the pattern. The possible values are the following:
+ c – Perform case-sensitive matching. The default is to use case-sensitive matching.
+ i – Perform case-insensitive matching.
+ p – Interpret the pattern with Perl Compatible Regular Expression (PCRE) dialect.
## Return type
Integer
## Example
The following example counts the number of times a three-letter sequence occurs.
```
SELECT regexp_count('abcdefghijklmnopqrstuvwxyz', '[a-z]{3}');
regexp_count
--------------
8
```
The following example counts the number of times the top-level domain name is either `org` or `edu`.
```
SELECT email, regexp_count(email,'@[^.]*\\.(org|edu)')FROM users
ORDER BY userid LIMIT 4;
email | regexp_count
-----------------------------------------------+--------------
Etiam.laoreet.libero@sodalesMaurisblandit.edu | 1
Suspendisse.tristique@nonnisiAenean.edu | 1
amet.faucibus.ut@condimentumegetvolutpat.ca | 0
sed@lacusUtnec.ca | 0
```
The following example counts the occurrences of the string `FOX`, using case-insensitive matching.
```
SELECT regexp_count('the fox', 'FOX', 1, 'i');
regexp_count
--------------
1
```
The following example uses a pattern written in the PCRE dialect to locate words containing at least one number and one lowercase letter. It uses the `?=` operator, which has a specific look-ahead connotation in PCRE. This example counts the number of occurrences of such words, with case-sensitive matching.
```
SELECT regexp_count('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 'p');
regexp_count
--------------
2
```
The following example uses a pattern written in the PCRE dialect to locate words containing at least one number and one lowercase letter. It uses the `?=` operator, which has a specific connotation in PCRE. This example counts the number of occurrences of such words, but differs from the previous example in that it uses case-insensitive matching.
```
SELECT regexp_count('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 'ip');
regexp_count
--------------
3
```
# REGEXP\$1INSTR function
Searches a string for a regular expression pattern and returns an integer that indicates the beginning position or ending position of the matched substring. If no match is found, then the function returns 0. REGEXP\$1INSTR is similar to the [POSITION](POSITION.md) function, but lets you search a string for a regular expression pattern.
## Syntax
```
REGEXP_INSTR ( source_string, pattern [, position [, occurrence] [, option [, parameters ] ] ] ] )
```
## Arguments
*source\$1string*
A string expression, such as a column name, to be searched.
*pattern*
A string literal that represents a regular expression pattern.
*position*
A positive integer that indicates the position within *source\$1string* to begin searching. The position is based on the number of characters, not bytes, so that multibyte characters are counted as single characters. The default is 1. If *position* is less than 1, the search begins at the first character of *source\$1string*. If *position* is greater than the number of characters in *source\$1string*, the result is 0.
*occurrence*
A positive integer that indicates which occurrence of the pattern to use. REGEXP\$1INSTR skips the first *occurrence* -1 matches. The default is 1. If *occurrence* is less than 1 or greater than the number of characters in *source\$1string*, the search is ignored and the result is 0.
*option*
A value that indicates whether to return the position of the first character of the match (`0`) or the position of the first character following the end of the match (`1`). A nonzero value is the same as 1. The default value is 0.
*parameters*
One or more string literals that indicate how the function matches the pattern. The possible values are the following:
+ c – Perform case-sensitive matching. The default is to use case-sensitive matching.
+ i – Perform case-insensitive matching.
+ e – Extract a substring using a subexpression.
If *pattern* includes a subexpression, REGEXP\$1INSTR matches a substring using the first subexpression in *pattern*. REGEXP\$1INSTR considers only the first subexpression; additional subexpressions are ignored. If the pattern doesn't have a subexpression, REGEXP\$1INSTR ignores the 'e' parameter.
+ p – Interpret the pattern with Perl Compatible Regular Expression (PCRE) dialect.
## Return type
Integer
## Example
The following example searches for the `@` character that begins a domain name and returns the starting position of the first match.
```
SELECT email, regexp_instr(email, '@[^.]*')
FROM users
ORDER BY userid LIMIT 4;
email | regexp_instr
-----------------------------------------------+--------------
Etiam.laoreet.libero@example.com | 21
Suspendisse.tristique@nonnisiAenean.edu | 22
amet.faucibus.ut@condimentumegetvolutpat.ca | 17
sed@lacusUtnec.ca | 4
```
The following example searches for variants of the word `Center` and returns the starting position of the first match.
```
SELECT venuename, regexp_instr(venuename,'[cC]ent(er|re)$')
FROM venue
WHERE regexp_instr(venuename,'[cC]ent(er|re)$') > 0
ORDER BY venueid LIMIT 4;
venuename | regexp_instr
-----------------------+--------------
The Home Depot Center | 16
Izod Center | 6
Wachovia Center | 10
Air Canada Centre | 12
```
The following example finds the starting position of the first occurrence of the string `FOX`, using case-insensitive matching logic.
```
SELECT regexp_instr('the fox', 'FOX', 1, 1, 0, 'i');
regexp_instr
--------------
5
```
The following example uses a pattern written in PCRE dialect to locate words containing at least one number and one lowercase letter. It uses the `?=` operator, which has a specific look-ahead connotation in PCRE. This example finds the starting position of the second such word.
```
SELECT regexp_instr('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 2, 0, 'p');
regexp_instr
--------------
21
```
The following example uses a pattern written in PCRE dialect to locate words containing at least one number and one lowercase letter. It uses the `?=` operator, which has a specific look-ahead connotation in PCRE. This example finds the starting position of the second such word, but differs from the previous example in that it uses case-insensitive matching.
```
SELECT regexp_instr('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 2, 0, 'ip');
regexp_instr
--------------
15
```
# REGEXP\$1REPLACE function
Searches a string for a regular expression pattern and replaces every occurrence of the pattern with the specified string. REGEXP\$1REPLACE is similar to the [REPLACE function](REPLACE.md), but lets you search a string for a regular expression pattern.
REGEXP\$1REPLACE is similar to the [TRANSLATE function](TRANSLATE.md) and the [REPLACE function](REPLACE.md), except that TRANSLATE makes multiple single-character substitutions and REPLACE substitutes one entire string with another string, while REGEXP\$1REPLACE lets you search a string for a regular expression pattern.
## Syntax
```
REGEXP_REPLACE ( source_string, pattern [, replace_string [ , position [, parameters ] ] ] )
```
## Arguments
*source\$1string*
A string expression, such as a column name, to be searched.
*pattern*
A string literal that represents a regular expression pattern.
*replace\$1string*
A string expression, such as a column name, that will replace each occurrence of pattern. The default is an empty string ( "" ).
*position*
A positive integer that indicates the position within *source\$1string* to begin searching. The position is based on the number of characters, not bytes, so that multibyte characters are counted as single characters. The default is 1. If *position* is less than 1, the search begins at the first character of *source\$1string*. If *position* is greater than the number of characters in *source\$1string*, the result is *source\$1string*.
*parameters*
One or more string literals that indicate how the function matches the pattern. The possible values are the following:
+ c – Perform case-sensitive matching. The default is to use case-sensitive matching.
+ i – Perform case-insensitive matching.
+ p – Interpret the pattern with Perl Compatible Regular Expression (PCRE) dialect.
## Return type
VARCHAR
If either *pattern* or *replace\$1string* is NULL, the return is NULL.
## Example
The following example deletes the `@` and domain name from email addresses.
```
SELECT email, regexp_replace(email, '@.*\\.(org|gov|com|edu|ca)$')
FROM users
ORDER BY userid LIMIT 4;
email | regexp_replace
-----------------------------------------------+----------------
Etiam.laoreet.libero@sodalesMaurisblandit.edu | Etiam.laoreet.libero
Suspendisse.tristique@nonnisiAenean.edu | Suspendisse.tristique
amet.faucibus.ut@condimentumegetvolutpat.ca | amet.faucibus.ut
sed@lacusUtnec.ca | sed
```
The following example replaces the domain names of email addresses with this value: `internal.company.com`.
```
SELECT email, regexp_replace(email, '@.*\\.[[:alpha:]]{2,3}',
'@internal.company.com') FROM users
ORDER BY userid LIMIT 4;
email | regexp_replace
-----------------------------------------------+--------------------------------------------
Etiam.laoreet.libero@sodalesMaurisblandit.edu | Etiam.laoreet.libero@internal.company.com
Suspendisse.tristique@nonnisiAenean.edu | Suspendisse.tristique@internal.company.com
amet.faucibus.ut@condimentumegetvolutpat.ca | amet.faucibus.ut@internal.company.com
sed@lacusUtnec.ca | sed@internal.company.com
```
The following example replaces all occurrences of the string `FOX` within the value `quick brown fox`, using case-insensitive matching.
```
SELECT regexp_replace('the fox', 'FOX', 'quick brown fox', 1, 'i');
regexp_replace
---------------------
the quick brown fox
```
The following example uses a pattern written in the PCRE dialect to locate words containing at least one number and one lowercase letter. It uses the `?=` operator, which has a specific look-ahead connotation in PCRE. This example replaces each occurrence of such a word with the value `[hidden]`.
```
SELECT regexp_replace('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', '[hidden]', 1, 'p');
regexp_replace
-------------------------------
[hidden] plain A1234 [hidden]
```
The following example uses a pattern written in the PCRE dialect to locate words containing at least one number and one lowercase letter. It uses the `?=` operator, which has a specific look-ahead connotation in PCRE. This example replaces each occurrence of such a word with the value `[hidden]`, but differs from the previous example in that it uses case-insensitive matching.
```
SELECT regexp_replace('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', '[hidden]', 1, 'ip');
regexp_replace
----------------------------------
[hidden] plain [hidden] [hidden]
```
# REGEXP\$1SUBSTR function
Returns characters from a string by searching it for a regular expression pattern. REGEXP\$1SUBSTR is similar to the [SUBSTRING function](SUBSTRING.md) function, but lets you search a string for a regular expression pattern. If the function can't match the regular expression to any characters in the string, it returns an empty string.
## Syntax
```
REGEXP_SUBSTR ( source_string, pattern [, position [, occurrence [, parameters ] ] ] )
```
## Arguments
*source\$1string*
A string expression to be searched.
*pattern*
A string literal that represents a regular expression pattern.
*position*
A positive integer that indicates the position within *source\$1string* to begin searching. The position is based on the number of characters, not bytes, so that multi-byte characters are counted as single characters. The default is 1. If *position* is less than 1, the search begins at the first character of *source\$1string*. If *position* is greater than the number of characters in *source\$1string*, the result is an empty string ("").
*occurrence*
A positive integer that indicates which occurrence of the pattern to use. REGEXP\$1SUBSTR skips the first *occurrence* -1 matches. The default is 1. If *occurrence* is less than 1 or greater than the number of characters in *source\$1string*, the search is ignored and the result is NULL.
*parameters*
One or more string literals that indicate how the function matches the pattern. The possible values are the following:
+ c – Perform case-sensitive matching. The default is to use case-sensitive matching.
+ i – Perform case-insensitive matching.
+ e – Extract a substring using a subexpression.
If *pattern* includes a subexpression, REGEXP\$1SUBSTR matches a substring using the first subexpression in *pattern*. A subexpression is an expression within the pattern that is bracketed with parentheses. For example, for the pattern `'This is a (\\w+)'` matches the first expression with the string `'This is a '` followed by a word. Instead of returning *pattern*, REGEXP\$1SUBSTR with the `e` parameter returns only the string inside the subexpression.
REGEXP\$1SUBSTR considers only the first subexpression; additional subexpressions are ignored. If the pattern doesn't have a subexpression, REGEXP\$1SUBSTR ignores the 'e' parameter.
+ p – Interpret the pattern with Perl Compatible Regular Expression (PCRE) dialect.
## Return type
VARCHAR
## Example
The following example returns the portion of an email address between the @ character and the domain extension.
```
SELECT email, regexp_substr(email,'@[^.]*')
FROM users
ORDER BY userid LIMIT 4;
email | regexp_substr
-----------------------------------------------+--------------------------
Etiam.laoreet.libero@sodalesMaurisblandit.edu | @sodalesMaurisblandit
Suspendisse.tristique@nonnisiAenean.edu | @nonnisiAenean
amet.faucibus.ut@condimentumegetvolutpat.ca | @condimentumegetvolutpat
sed@lacusUtnec.ca | @lacusUtnec
```
The following example returns the portion of the input corresponding to the first occurrence of the string `FOX`, using case-insensitive matching.
```
SELECT regexp_substr('the fox', 'FOX', 1, 1, 'i');
regexp_substr
---------------
fox
```
The following example returns the first portion of the input that begins with lowercase letters. This is functionally identical to the same SELECT statement without the `c` parameter.
```
SELECT regexp_substr('THE SECRET CODE IS THE LOWERCASE PART OF 1931abc0EZ.', '[a-z]+', 1, 1, 'c');
regexp_substr
---------------
abc
```
The following example uses a pattern written in the PCRE dialect to locate words containing at least one number and one lowercase letter. It uses the `?=` operator, which has a specific look-ahead connotation in PCRE. This example returns the portion of the input corresponding to the second such word.
```
SELECT regexp_substr('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 2, 'p');
regexp_substr
---------------
a1234
```
The following example uses a pattern written in the PCRE dialect to locate words containing at least one number and one lowercase letter. It uses the `?=` operator, which has a specific look-ahead connotation in PCRE. This example returns the portion of the input corresponding to the second such word, but differs from the previous example in that it uses case-insensitive matching.
```
SELECT regexp_substr('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', 1, 2, 'ip');
regexp_substr
---------------
A1234
```
The following example uses a subexpression to find the second string matching the pattern `'this is a (\\w+)'` using case-insensitive matching. It returns the subexpression inside the parentheses.
```
select regexp_substr(
'This is a cat, this is a dog. This is a mouse.',
'this is a (\\w+)', 1, 2, 'ie');
regexp_substr
---------------
dog
```
# REPEAT function
Repeats a string the specified number of times. If the input parameter is numeric, REPEAT treats it as a string.
## Syntax
```
REPEAT(string, integer)
```
## Arguments
*string*
The first input parameter is the string to be repeated.
*integer*
The second parameter is an integer indicating the number of times to repeat the string.
## Return type
The REPEAT function returns a string.
## Examples
The following example repeats the value of the CATID column in the CATEGORY table three times:
```
select catid, repeat(catid,3)
from category
order by 1,2;
catid | repeat
-------+--------
1 | 111
2 | 222
3 | 333
4 | 444
5 | 555
6 | 666
7 | 777
8 | 888
9 | 999
10 | 101010
11 | 111111
(11 rows)
```
# REPLACE function
Replaces all occurrences of a set of characters within an existing string with other specified characters.
REPLACE is similar to the [TRANSLATE function](TRANSLATE.md) and the [REGEXP\$1REPLACE function](REGEXP_REPLACE.md), except that TRANSLATE makes multiple single-character substitutions and REGEXP\$1REPLACE lets you search a string for a regular expression pattern, while REPLACE substitutes one entire string with another string.
## Syntax
```
REPLACE(string1, old_chars, new_chars)
```
## Arguments
*string*
CHAR or VARCHAR string to be searched search
*old\$1chars*
CHAR or VARCHAR string to replace.
*new\$1chars*
New CHAR or VARCHAR string replacing the *old\$1string*.
## Return type
VARCHAR
If either *old\$1chars* or *new\$1chars* is NULL, the return is NULL.
## Examples
The following example converts the string `Shows` to `Theatre` in the CATGROUP field:
```
select catid, catgroup,
replace(catgroup, 'Shows', 'Theatre')
from category
order by 1,2,3;
catid | catgroup | replace
-------+----------+----------
1 | Sports | Sports
2 | Sports | Sports
3 | Sports | Sports
4 | Sports | Sports
5 | Sports | Sports
6 | Shows | Theatre
7 | Shows | Theatre
8 | Shows | Theatre
9 | Concerts | Concerts
10 | Concerts | Concerts
11 | Concerts | Concerts
(11 rows)
```
# REVERSE function
The REVERSE function operates on a string and returns the characters in reverse order. For example, `reverse('abcde')` returns `edcba`. This function works on numeric and date data types as well as character data types; however, in most cases it has practical value for character strings.
## Syntax
```
REVERSE ( expression )
```
## Argument
*expression*
An expression with a character, date, timestamp, or numeric data type that represents the target of the character reversal. All expressions are implicitly converted to variable-length character strings. Trailing blanks in fixed-width character strings are ignored.
## Return type
REVERSE returns a VARCHAR.
## Examples
Select five distinct city names and their corresponding reversed names from the USERS table:
```
select distinct city as cityname, reverse(cityname)
from users order by city limit 5;
cityname | reverse
---------+----------
Aberdeen | needrebA
Abilene | enelibA
Ada | adA
Agat | tagA
Agawam | mawagA
(5 rows)
```
Select five sales IDs and their corresponding reversed IDs cast as character strings:
```
select salesid, reverse(salesid)::varchar
from sales order by salesid desc limit 5;
salesid | reverse
--------+---------
172456 | 654271
172455 | 554271
172454 | 454271
172453 | 354271
172452 | 254271
(5 rows)
```
# RTRIM function
The RTRIM function trims a specified set of characters from the end of a string. Removes the longest string containing only characters in the trim characters list. Trimming is complete when a trim character doesn't appear in the input string.
## Syntax
```
RTRIM( string, trim_chars )
```
## Arguments
*string*
A string column, expression, or string literal to be trimmed.
*trim\$1chars*
A string column, expression, or string literal that represents the characters to be trimmed from the end of *string*. If not specified, a space is used as the trim character.
## Return type
A string that is the same data type as the *string* argument.
## Example
The following example trims leading and trailing blanks from the string `' abc '`:
```
select ' abc ' as untrim, rtrim(' abc ') as trim;
untrim | trim
----------+------
abc | abc
```
The following example removes the trailing `'xyz'` strings from the string `'xyzaxyzbxyzcxyz'`. The trailing occurrences of `'xyz'` are removed, but occurrences that are internal within the string are not removed.
```
select 'xyzaxyzbxyzcxyz' as untrim,
rtrim('xyzaxyzbxyzcxyz', 'xyz') as trim;
untrim | trim
-----------------+-----------
xyzaxyzbxyzcxyz | xyzaxyzbxyzc
```
The following example removes the trailing parts from the string `'setuphistorycassettes'` that match any of the characters in the *trim\$1chars* list `'tes'`. Any `t`, `e`, or `s` that occur before another character that is not in the *trim\$1chars* list at the ending of the input string are removed.
```
SELECT rtrim('setuphistorycassettes', 'tes');
rtrim
-----------------
setuphistoryca
```
The following example trims the characters 'Park' from the end of VENUENAME where present:
```
select venueid, venuename, rtrim(venuename, 'Park')
from venue
order by 1, 2, 3
limit 10;
venueid | venuename | rtrim
--------+----------------------------+-------------------------
1 | Toyota Park | Toyota
2 | Columbus Crew Stadium | Columbus Crew Stadium
3 | RFK Stadium | RFK Stadium
4 | CommunityAmerica Ballpark | CommunityAmerica Ballp
5 | Gillette Stadium | Gillette Stadium
6 | New York Giants Stadium | New York Giants Stadium
7 | BMO Field | BMO Field
8 | The Home Depot Center | The Home Depot Cente
9 | Dick's Sporting Goods Park | Dick's Sporting Goods
10 | Pizza Hut Park | Pizza Hut
```
Note that RTRIM removes any of the characters `P`, `a`, `r`, or `k` when they appear at the end of a VENUENAME.
# SPLIT function
The SPLIT function allows you to extract substrings from a larger string and work with them as an array. The SPLIT function is useful when you need to break down a string into individual components based on a specific delimiter or pattern.
## Syntax
```
split(str, regex, limit)
```
## Arguments
*str*
A string expression to split.
*regex*
A string representing a regular expression. The *regex* string should be a Java regular expression.
*limit*
An integer expression which controls the number of times the *regex* is applied.
+ limit > 0: The resulting array's length will not be more than limit, and the resulting array's last entry will contain all input beyond the last matched *regex*.
+ limit <= 0: *regex* will be applied as many times as possible, and the resulting array can be of any size.
## Return type
The SPLIT function returns an ARRAY.
If `limit > 0`: The resulting array’s length will not be more than limit, and the resulting array’s last entry will contain all input beyond the last matched regex.
If `limit <= 0`: regex will be applied as many times as possible, and the resulting array can be of any size.
## Example
In this example, the SPLIT function splits the input string `'oneAtwoBthreeC'` wherever it encounters the characters `'A'`, `'B'`, or `'C'` (as specified by the regular expression pattern `'[ABC]'`). The resulting output is an array of four elements: `"one"`, `"two"`, `"three"`, and an empty string `""`.
```
SELECT split('oneAtwoBthreeC', '[ABC]');
["one","two","three",""]
```
# SPLIT\$1PART function
Splits a string on the specified delimiter and returns the part at the specified position.
## Syntax
```
SPLIT_PART(string, delimiter, position)
```
## Arguments
*string*
A string column, expression, or string literal to be split. The string can be CHAR or VARCHAR.
*delimiter*
The delimiter string indicating sections of the input *string*.
If *delimiter* is a literal, enclose it in single quotation marks.
*position*
Position of the portion of *string* to return (counting from 1). Must be an integer greater than 0. If *position* is larger than the number of string portions, SPLIT\$1PART returns an empty string. If *delimiter* is not found in *string*, then the returned value contains the contents of the specified part, which might be the entire *string* or an empty value.
## Return type
A CHAR or VARCHAR string, the same as the *string* parameter.
## Examples
The following example splits a string literal into parts using the `$` delimiter and returns the second part.
```
select split_part('abc$def$ghi','$',2)
split_part
----------
def
```
The following example splits a string literal into parts using the `$` delimiter. It returns an empty string because part `4` is not found.
```
select split_part('abc$def$ghi','$',4)
split_part
----------
```
The following example splits a string literal into parts using the `#` delimiter. It returns the entire string, which is the first part, because the delimiter is not found.
```
select split_part('abc$def$ghi','#',1)
split_part
------------
abc$def$ghi
```
The following example splits the timestamp field LISTTIME into year, month, and day components.
```
select listtime, split_part(listtime,'-',1) as year,
split_part(listtime,'-',2) as month,
split_part(split_part(listtime,'-',3),' ',1) as day
from listing limit 5;
listtime | year | month | day
---------------------+------+-------+------
2008-03-05 12:25:29 | 2008 | 03 | 05
2008-09-09 08:03:36 | 2008 | 09 | 09
2008-09-26 05:43:12 | 2008 | 09 | 26
2008-10-04 02:00:30 | 2008 | 10 | 04
2008-01-06 08:33:11 | 2008 | 01 | 06
```
The following example selects the LISTTIME timestamp field and splits it on the `'-'` character to get the month (the second part of the LISTTIME string), then counts the number of entries for each month:
```
select split_part(listtime,'-',2) as month, count(*)
from listing
group by split_part(listtime,'-',2)
order by 1, 2;
month | count
-------+-------
01 | 18543
02 | 16620
03 | 17594
04 | 16822
05 | 17618
06 | 17158
07 | 17626
08 | 17881
09 | 17378
10 | 17756
11 | 12912
12 | 4589
```
# SUBSTRING function
Returns the subset of a string based on the specified start position.
If the input is a character string, the start position and number of characters extracted are based on characters, not bytes, so that multi-byte characters are counted as single characters. If the input is a binary expression, the start position and extracted substring are based on bytes. You can't specify a negative length, but you can specify a negative starting position.
## Syntax
```
SUBSTRING(charactestring FROM start_position [ FOR numbecharacters ] )
```
```
SUBSTRING(charactestring, start_position, numbecharacters )
```
```
SUBSTRING(binary_expression, start_byte, numbebytes )
```
```
SUBSTRING(binary_expression, start_byte )
```
## Arguments
*charactestring*
The string to be searched. Non-character data types are treated like a string.
*start\$1position*
The position within the string to begin the extraction, starting at 1. The *start\$1position* is based on the number of characters, not bytes, so that multi-byte characters are counted as single characters. This number can be negative.
*numbecharacters*
The number of characters to extract (the length of the substring). The *numbecharacters* is based on the number of characters, not bytes, so that multi-byte characters are counted as single characters. This number cannot be negative.
*start\$1byte*
The position within the binary expression to begin the extraction, starting at 1. This number can be negative.
*numbebytes*
The number of bytes to extract, that is, the length of the substring. This number can't be negative.
## Return type
VARCHAR
## Usage notes for character strings
The following example returns a four-character string beginning with the sixth character.
```
select substring('caterpillar',6,4);
substring
-----------
pill
(1 row)
```
If the *start\$1position* \$1 *numbecharacters* exceeds the length of the *string*, SUBSTRING returns a substring starting from the *start\$1position* until the end of the string. For example:
```
select substring('caterpillar',6,8);
substring
-----------
pillar
(1 row)
```
If the `start_position` is negative or 0, the SUBSTRING function returns a substring beginning at the first character of string with a length of `start_position` \$1 `numbecharacters` -1. For example:
```
select substring('caterpillar',-2,6);
substring
-----------
cat
(1 row)
```
If `start_position` \$1 `numbecharacters` -1 is less than or equal to zero, SUBSTRING returns an empty string. For example:
```
select substring('caterpillar',-5,4);
substring
-----------
(1 row)
```
## Examples
The following example returns the month from the LISTTIME string in the LISTING table:
```
select listid, listtime,
substring(listtime, 6, 2) as month
from listing
order by 1, 2, 3
limit 10;
listid | listtime | month
--------+---------------------+-------
1 | 2008-01-24 06:43:29 | 01
2 | 2008-03-05 12:25:29 | 03
3 | 2008-11-01 07:35:33 | 11
4 | 2008-05-24 01:18:37 | 05
5 | 2008-05-17 02:29:11 | 05
6 | 2008-08-15 02:08:13 | 08
7 | 2008-11-15 09:38:15 | 11
8 | 2008-11-09 05:07:30 | 11
9 | 2008-09-09 08:03:36 | 09
10 | 2008-06-17 09:44:54 | 06
(10 rows)
```
The following example is the same as above, but uses the FROM...FOR option:
```
select listid, listtime,
substring(listtime from 6 for 2) as month
from listing
order by 1, 2, 3
limit 10;
listid | listtime | month
--------+---------------------+-------
1 | 2008-01-24 06:43:29 | 01
2 | 2008-03-05 12:25:29 | 03
3 | 2008-11-01 07:35:33 | 11
4 | 2008-05-24 01:18:37 | 05
5 | 2008-05-17 02:29:11 | 05
6 | 2008-08-15 02:08:13 | 08
7 | 2008-11-15 09:38:15 | 11
8 | 2008-11-09 05:07:30 | 11
9 | 2008-09-09 08:03:36 | 09
10 | 2008-06-17 09:44:54 | 06
(10 rows)
```
You can't use SUBSTRING to predictably extract the prefix of a string that might contain multi-byte characters because you need to specify the length of a multi-byte string based on the number of bytes, not the number of characters. To extract the beginning segment of a string based on the length in bytes, you can CAST the string as VARCHAR(*byte\$1length*) to truncate the string, where *byte\$1length* is the required length. The following example extracts the first 5 bytes from the string `'Fourscore and seven'`.
```
select cast('Fourscore and seven' as varchar(5));
varchar
-------
Fours
```
The following example returns the first name `Ana` which appears after the last space in the input string `Silva, Ana`.
```
select reverse(substring(reverse('Silva, Ana'), 1, position(' ' IN reverse('Silva, Ana'))))
reverse
-----------
Ana
```
# TRANSLATE function
For a given expression, replaces all occurrences of specified characters with specified substitutes. Existing characters are mapped to replacement characters by their positions in the *characters\$1to\$1replace* and *characters\$1to\$1substitute* arguments. If more characters are specified in the *characters\$1to\$1replace* argument than in the *characters\$1to\$1substitute* argument, the extra characters from the *characters\$1to\$1replace* argument are omitted in the return value.
TRANSLATE is similar to the [REPLACE function](REPLACE.md) and the [REGEXP\$1REPLACE function](REGEXP_REPLACE.md), except that REPLACE substitutes one entire string with another string and REGEXP\$1REPLACE lets you search a string for a regular expression pattern, while TRANSLATE makes multiple single-character substitutions.
If any argument is null, the return is NULL.
## Syntax
```
TRANSLATE ( expression, characters_to_replace, characters_to_substitute )
```
## Arguments
*expression*
The expression to be translated.
*characters\$1to\$1replace*
A string containing the characters to be replaced.
*characters\$1to\$1substitute*
A string containing the characters to substitute.
## Return type
VARCHAR
## Examples
The following example replaces several characters in a string:
```
select translate('mint tea', 'inea', 'osin');
translate
-----------
most tin
```
The following example replaces the at sign (@) with a period for all values in a column:
```
select email, translate(email, '@', '.') as obfuscated_email
from users limit 10;
email obfuscated_email
-------------------------------------------------------------------------------------------
Etiam.laoreet.libero@sodalesMaurisblandit.edu Etiam.laoreet.libero.sodalesMaurisblandit.edu
amet.faucibus.ut@condimentumegetvolutpat.ca amet.faucibus.ut.condimentumegetvolutpat.ca
turpis@accumsanlaoreet.org turpis.accumsanlaoreet.org
ullamcorper.nisl@Cras.edu ullamcorper.nisl.Cras.edu
arcu.Curabitur@senectusetnetus.com arcu.Curabitur.senectusetnetus.com
ac@velit.ca ac.velit.ca
Aliquam.vulputate.ullamcorper@amalesuada.org Aliquam.vulputate.ullamcorper.amalesuada.org
vel.est@velitegestas.edu vel.est.velitegestas.edu
dolor.nonummy@ipsumdolorsit.ca dolor.nonummy.ipsumdolorsit.ca
et@Nunclaoreet.ca et.Nunclaoreet.ca
```
The following example replaces spaces with underscores and strips out periods for all values in a column:
```
select city, translate(city, ' .', '_') from users
where city like 'Sain%' or city like 'St%'
group by city
order by city;
city translate
--------------+------------------
Saint Albans Saint_Albans
Saint Cloud Saint_Cloud
Saint Joseph Saint_Joseph
Saint Louis Saint_Louis
Saint Paul Saint_Paul
St. George St_George
St. Marys St_Marys
St. Petersburg St_Petersburg
Stafford Stafford
Stamford Stamford
Stanton Stanton
Starkville Starkville
Statesboro Statesboro
Staunton Staunton
Steubenville Steubenville
Stevens Point Stevens_Point
Stillwater Stillwater
Stockton Stockton
Sturgis Sturgis
```
# TRIM function
Trims a string by removing leading and trailing blanks or by removing leading and trailing characters that match an optional specified string.
## Syntax
```
TRIM( [ BOTH ] [ trim_chars FROM ] string
```
## Arguments
*trim\$1chars*
(Optional) The characters to be trimmed from the string. If this parameter is omitted, blanks are trimmed.
*string*
The string to be trimmed.
## Return type
The TRIM function returns a VARCHAR or CHAR string. If you use the TRIM function with a SQL command, AWS Clean Rooms implicitly converts the results to VARCHAR. If you use the TRIM function in the SELECT list for a SQL function, AWS Clean Rooms does not implicitly convert the results, and you might need to perform an explicit conversion to avoid a data type mismatch error. See the [CAST function](CAST_function.md) function for information about explicit conversions.
## Example
The following example trims leading and trailing blanks from the string `' abc '`:
```
select ' abc ' as untrim, trim(' abc ') as trim;
untrim | trim
----------+------
abc | abc
```
The following example removes the double quotation marks that surround the string `"dog"`:
```
select trim('"' FROM '"dog"');
btrim
-------
dog
```
TRIM removes any of the characters in *trim\$1chars* when they appear at the beginning of *string*. The following example trims the characters 'C', 'D', and 'G' when they appear at the beginning of VENUENAME, which is a VARCHAR column.
```
select venueid, venuename, trim(venuename, 'CDG')
from venue
where venuename like '%Park'
order by 2
limit 7;
venueid | venuename | btrim
--------+----------------------------+--------------------------
121 | ATT Park | ATT Park
109 | Citizens Bank Park | itizens Bank Park
102 | Comerica Park | omerica Park
9 | Dick's Sporting Goods Park | ick's Sporting Goods Park
97 | Fenway Park | Fenway Park
112 | Great American Ball Park | reat American Ball Park
114 | Miller Park | Miller Park
```
# UPPER function
Converts a string to uppercase. UPPER supports UTF-8 multibyte characters, up to a maximum of four bytes per character.
## Syntax
```
UPPER(string)
```
## Arguments
*string*
The input parameter is a VARCHAR string (or any other data type, such as CHAR, that can be implicitly converted to VARCHAR).
## Return type
The UPPER function returns a character string that is the same data type as the input string.
## Examples
The following example converts the CATNAME field to uppercase:
```
select catname, upper(catname) from category order by 1,2;
catname | upper
----------+-----------
Classical | CLASSICAL
Jazz | JAZZ
MLB | MLB
MLS | MLS
Musicals | MUSICALS
NBA | NBA
NFL | NFL
NHL | NHL
Opera | OPERA
Plays | PLAYS
Pop | POP
(11 rows)
```
# UUID function
The UUID function generates a Universally Unique Identifier (UUID).
UUIDs are globally unique identifiers that are commonly used to provide unique identifiers for various purposes, such as:
+ Identifying database records or other data entities.
+ Generating unique names or keys for files, directories, or other resources.
+ Tracking and correlating data across distributed systems.
+ Providing unique identifiers for network packets, software components, or other digital assets.
The UUID function generates a UUID value that is unique with a very high probability, even across distributed systems and over long periods of time. UUIDs are typically generated using a combination of the current timestamp, the computer's network address, and other random or pseudo-random data, ensuring that each generated UUID is highly unlikely to conflict with any other UUID.
In the context of a SQL query, the UUID function can be used to generate unique identifiers for new records being inserted into a database, or to provide unique keys for data partitioning, indexing, or other purposes where a unique identifier is required.
**Note**
The UUID function is non-deterministic.
## Syntax
```
uuid()
```
## Arguments
The UUID function takes no argument.
## Return type
UUID returns a universally unique identifier (UUID) string. The value is returned as a canonical UUID 36-character string.
## Example
The following example generates a Universally Unique Identifier (UUID). The output is a 36-character string representing a Universally Unique Identifier.
```
SELECT uuid();
46707d92-02f4-4817-8116-a4c3b23e6266
```