Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# String functions
**Topics**
+ [
# \$1\$1 (Concatenation) operator
](r_concat_op.md)
+ [
# ASCII function
](r_ASCII.md)
+ [
# BPCHARCMP function
](r_BPCHARCMP.md)
+ [
# BTRIM function
](r_BTRIM.md)
+ [
# BTTEXT\$1PATTERN\$1CMP function
](r_BTTEXT_PATTERN_CMP.md)
+ [
# CHAR\$1LENGTH function
](r_CHAR_LENGTH.md)
+ [
# CHARACTER\$1LENGTH function
](r_CHARACTER_LENGTH.md)
+ [
# CHARINDEX function
](r_CHARINDEX.md)
+ [
# CHR function
](r_CHR.md)
+ [
# COLLATE function
](r_COLLATE.md)
+ [
# CONCAT function
](r_CONCAT.md)
+ [
# CRC32 function
](crc32-function.md)
+ [
# DIFFERENCE function
](DIFFERENCE.md)
+ [
# INITCAP function
](r_INITCAP.md)
+ [
# LEFT and RIGHT functions
](r_LEFT.md)
+ [
# LEN function
](r_LEN.md)
+ [
# LENGTH function
](r_LENGTH.md)
+ [
# LOWER function
](r_LOWER.md)
+ [
# LPAD and RPAD functions
](r_LPAD.md)
+ [
# LTRIM function
](r_LTRIM.md)
+ [
# OCTETINDEX function
](OCTETINDEX.md)
+ [
# OCTET\$1LENGTH function
](r_OCTET_LENGTH.md)
+ [
# POSITION function
](r_POSITION.md)
+ [
# QUOTE\$1IDENT function
](r_QUOTE_IDENT.md)
+ [
# QUOTE\$1LITERAL function
](r_QUOTE_LITERAL.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
](r_REPEAT.md)
+ [
# REPLACE function
](r_REPLACE.md)
+ [
# REPLICATE function
](r_REPLICATE.md)
+ [
# REVERSE function
](r_REVERSE.md)
+ [
# RTRIM function
](r_RTRIM.md)
+ [
# SOUNDEX function
](SOUNDEX.md)
+ [
# SPLIT\$1PART function
](SPLIT_PART.md)
+ [
# STRPOS function
](r_STRPOS.md)
+ [
# STRTOL function
](r_STRTOL.md)
+ [
# SUBSTRING function
](r_SUBSTRING.md)
+ [
# TEXTLEN function
](r_TEXTLEN.md)
+ [
# TRANSLATE function
](r_TRANSLATE.md)
+ [
# TRIM function
](r_TRIM.md)
+ [
# UPPER function
](r_UPPER.md)
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.
**Deprecated leader node-only functions**
The following string functions are deprecated because they run only on the leader node. For more information, see [Leader node–only functions](c_SQL_functions_leader_node_only.md)
+ GET\$1BYTE
+ SET\$1BIT
+ SET\$1BYTE
+ TO\$1ASCII
# \$1\$1 (Concatenation) operator
Concatenates two expressions on either side of the `||` symbol and returns the concatenated expression.
Similar to [CONCAT function](r_CONCAT.md).
**Note**
If one or both of the expressions is null, the result of the concatenation is `NULL`.
## Syntax
```
expression1 || expression2
```
## Arguments
*expression1*
A `CHAR` string, a `VARCHAR` string, a binary expression, or an expression that evaluates to one of these types.
*expression2*
A `CHAR` string, a `VARCHAR` string, a binary expression, or an expression that evaluates to one of these types.
## Return type
The return type of the string is the same as the type of the input arguments. For example, concatenating two strings of type `VARCHAR` returns a string of type `VARCHAR`.
## Examples
The following examples use the USERS and VENUE tables from the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To concatenate the FIRSTNAME and LASTNAME fields from the USERS table in the sample database, use the following example.
```
SELECT (firstname || ' ' || lastname) as fullname
FROM users
ORDER BY 1
LIMIT 10;
+-----------------+
| fullname |
+-----------------+
| Aaron Banks |
| Aaron Booth |
| Aaron Browning |
| Aaron Burnett |
| Aaron Casey |
| Aaron Cash |
| Aaron Castro |
| Aaron Dickerson |
| Aaron Dixon |
| Aaron Dotson |
+-----------------+
```
To concatenate columns that might contain nulls, use the [NVL and COALESCE functions](r_NVL_function.md) expression. The following example uses NVL to return a `0` whenever `NULL` is encountered.
```
SELECT (venuename || ' seats ' || NVL(venueseats, 0)) as seating
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 |
+-------------------------------------+
```
# ASCII function
The ASCII function returns the ASCII code, or the Unicode code-point, of the first character in the string that you specify. The function returns `0` if the string is empty. It returns `NULL` if the string is null.
## Syntax
```
ASCII('string')
```
## Argument
*string*
A `CHAR` string or a `VARCHAR` string.
## Return type
INTEGER
## Examples
To return `NULL`, use the following example. The NULLIF function returns `NULL` if the two arguments are the same, so the input argument for the ASCII function is `NULL`. For more information, see [NULLIF function](r_NULLIF_function.md).
```
SELECT ASCII(NULLIF('',''));
+-------+
| ascii |
+-------+
| NULL |
+-------+
```
To return the ASCII code 0, use the following example.
```
SELECT ASCII('');
+-------+
| ascii |
+-------+
| 0 |
+-------+
```
To return the ASCII code 97 for the first letter of the word amazon, use the following example.
```
SELECT ASCII('amazon');
+-------+
| ascii |
+-------+
| 97 |
+-------+
```
To return the ASCII code 65 for the first letter of the word Amazon, use the following example.
```
SELECT ASCII('Amazon');
+-------+
| ascii |
+-------+
| 65 |
+-------+
```
# BPCHARCMP function
Compares the value of two strings and returns an integer. If the strings are identical, the function returns `0`. If the first string is greater alphabetically, the function returns `1`. If the second string is greater, the function returns `-1`.
For multibyte characters, the comparison is based on the byte encoding.
Synonym of [BTTEXT\$1PATTERN\$1CMP function](r_BTTEXT_PATTERN_CMP.md).
## Syntax
```
BPCHARCMP(string1, string2)
```
## Arguments
*string1*
A `CHAR` string or a `VARCHAR` string.
*string2*
A `CHAR` string or a `VARCHAR` string.
## Return type
INTEGER
## Examples
The following examples use the USERS table from the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To determine whether a user's first name is alphabetically greater than the user's last name for the first ten entries in the USERS table, use the following example. For entries where the string for FIRSTNAME is later alphabetically than the string for LASTNAME, the function returns `1`. If the LASTNAME is alphabetically later than FIRSTNAME, the function returns `-1`.
```
SELECT userid, firstname, lastname, BPCHARCMP(firstname, lastname)
FROM users
ORDER BY 1, 2, 3, 4
LIMIT 10;
+--------+-----------+-----------+-----------+
| userid | firstname | lastname | bpcharcmp |
+--------+-----------+-----------+-----------+
| 1 | Rafael | Taylor | -1 |
| 2 | Vladimir | Humphrey | 1 |
| 3 | Lars | Ratliff | -1 |
| 4 | Barry | Roy | -1 |
| 5 | Reagan | Hodge | 1 |
| 6 | Victor | Hernandez | 1 |
| 7 | Tamekah | Juarez | 1 |
| 8 | Colton | Roy | -1 |
| 9 | Mufutau | Watkins | -1 |
| 10 | Naida | Calderon | 1 |
+--------+-----------+-----------+-----------+
```
To return all entries in the USERS table where the function returns `0`, use the following example. The function returns `0` when FIRSTNAME is identical to LASTNAME.
```
SELECT userid, firstname, lastname,
BPCHARCMP(firstname, lastname)
FROM users
WHERE BPCHARCMP(firstname, lastname)=0
ORDER BY 1, 2, 3, 4;
+--------+-----------+----------+-----------+
| userid | firstname | lastname | bpcharcmp |
+--------+-----------+----------+-----------+
| 62 | Chase | Chase | 0 |
| 4008 | Whitney | Whitney | 0 |
| 12516 | Graham | Graham | 0 |
| 13570 | Harper | Harper | 0 |
| 16712 | Cooper | Cooper | 0 |
| 18359 | Chase | Chase | 0 |
| 27530 | Bradley | Bradley | 0 |
| 31204 | Harding | Harding | 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
```
# BTTEXT\$1PATTERN\$1CMP function
Synonym for the BPCHARCMP function.
See [BPCHARCMP function](r_BPCHARCMP.md) for details.
# CHAR\$1LENGTH function
Synonym of the LEN function.
See [LEN function](r_LEN.md).
# CHARACTER\$1LENGTH function
Synonym of the LEN function.
See [LEN function](r_LEN.md).
# CHARINDEX function
Returns the location of the specified substring within a string.
See [POSITION function](r_POSITION.md) and [STRPOS function](r_STRPOS.md) for similar functions.
## Syntax
```
CHARINDEX( substring, string )
```
## Arguments
*substring*
The substring to search for within the *string*.
*string*
The string or column to be searched.
## Return type
INTEGER
The CHARINDEX 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. CHARINDEX returns `0` if the substring is not found within the string.
## Examples
To return the position of the string `fish` within the word `dog`, use the following example.
```
SELECT CHARINDEX('fish', 'dog');
+-----------+
| charindex |
+-----------+
| 0 |
+-----------+
```
To return the position of the string `fish` within the word `dogfish`, use the following example.
```
SELECT CHARINDEX('fish', 'dogfish');
+-----------+
| charindex |
+-----------+
| 4 |
+-----------+
```
The following example uses the SALES table from the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To return the number of distinct sales transactions with a commission over 999.00 from the SALES table, use the following example. This command counts commissions greater than 999.00 by checking if the decimal is more than 4 places from the beginning of the commission value.
```
SELECT DISTINCT CHARINDEX('.', commission), COUNT (CHARINDEX('.', commission))
FROM sales
WHERE CHARINDEX('.', commission) > 4
GROUP BY CHARINDEX('.', commission)
ORDER BY 1,2;
+-----------+-------+
| charindex | count |
+-----------+-------+
| 5 | 629 |
+-----------+-------+
```
# CHR function
The CHR function returns the character that matches the ASCII code point value specified by the input parameter.
## Syntax
```
CHR(number)
```
## Argument
*number*
The input parameter is an `INTEGER` that represents an ASCII code point value.
## Return type
CHAR
The CHR function returns a `CHAR` string if an ASCII character matches the input value. If the input number has no ASCII match, the function returns `NULL`.
## Examples
To return the character that corresponds with ASCII code point 0, use the following example. Note that the CHR function returns `NULL` for the input `0`.
```
SELECT CHR(0);
+-----+
| chr |
+-----+
| |
+-----+
```
To return the character that corresponds with ASCII code point 65, use the following example.
```
SELECT CHR(65);
+-----+
| chr |
+-----+
| A |
+-----+
```
To return distinct event names that begin with a capital A (ASCII code point 65), use the following example. The following example uses the EVENT table from the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
```
SELECT DISTINCT eventname FROM event
WHERE SUBSTRING(eventname, 1, 1)=CHR(65) LIMIT 5;
+-----------------------+
| eventname |
+-----------------------+
| A Catered Affair |
| As You Like It |
| A Man For All Seasons |
| Alan Jackson |
| Armando Manzanero |
+-----------------------+
```
# COLLATE function
The COLLATE function overrides the collation of a string column or expression.
For information on how to create tables using database collation, see [CREATE TABLE](r_CREATE_TABLE_NEW.md).
For information on how to create databases using database collation, see [CREATE DATABASE](r_CREATE_DATABASE.md).
## Syntax
```
COLLATE( string, 'case_sensitive' | 'cs' | 'case_insensitive' | 'ci');
```
## Arguments
*string*
A string column or expression that you want to override.
*'case\$1sensitive'* \$1 *'cs'* \$1 *'case\$1insensitive'* \$1 *'ci'*
A string constant of a collation name. Amazon Redshift only supports the following values for this parameter:
+ *case\$1sensitive*
+ *cs*
+ *case\$1insensitive*
+ *ci*
*case\$1sensitive* and *cs* are interchangeable and yield the same results. Similarly, *case\$1insensitive* and *ci* are interchangeable and yield the same results.
## Return type
The COLLATE function returns `VARCHAR`, `CHAR`, or `SUPER` depending on the first input expression type. This function only changes the collation of the first input argument and won't change its output value.
## Examples
To create table T and define col1 in table T as `case_sensitive`, use the following example.
```
CREATE TABLE T ( col1 Varchar(20) COLLATE case_sensitive );
INSERT INTO T VALUES ('john'),('JOHN');
```
When you run the first query, Amazon Redshift only returns `john`. After the COLLATE function runs on col1, the collation becomes `case_insensitive`. The second query returns both `john` and `JOHN`.
```
SELECT * FROM T WHERE col1 = 'john';
+------+
| col1 |
+------+
| john |
+------+
SELECT * FROM T WHERE COLLATE(col1, 'case_insensitive') = 'john';
+------+
| col1 |
+------+
| john |
| JOHN |
+------+
```
To create table A and define col1 in table A as `case_insensitive`, use the following example.
```
CREATE TABLE A ( col1 Varchar(20) COLLATE case_insensitive );
INSERT INTO A VALUES ('john'),('JOHN');
```
When you run the first query, Amazon Redshift returns both `john` and `JOHN`. After the COLLATE function runs on col1, the collation becomes `case_sensitive`. The second query returns only `john`.
```
SELECT * FROM A WHERE col1 = 'john';
+------+
| col1 |
+------+
| john |
| JOHN |
+------+
SELECT * FROM A WHERE COLLATE(col1, 'case_sensitive') = 'john';
+------+
| col1 |
+------+
| john |
+------+
```
# 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.
## 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, Amazon Redshift tries to implicitly type casts one of the expressions. If values can't be cast, an error is returned.
## Usage notes
+ For both the CONCAT function and the concatenation operator, if one or both expressions is null, the result of the concatenation is null.
## 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';
?column?
-------------------
December 25, 2008
(1 row)
```
The following example uses a nested CONCAT function inside another CONCAT function 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](r_NVL_function.md), which returns a given value when it encounters NULL. 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)
```
The following example concatenates two binary expressions. Where `abc` is a binary value (with a hexadecimal representation of `616263`) and `def` is a binary value (with hexadecimal representation of `646566`). The result is automatically shown as the hexadecimal representation of the binary value.
```
SELECT CONCAT('abc'::VARBYTE, 'def'::VARBYTE);
concat
-------------------
616263646566
```
# CRC32 function
CRC32 is a function used for error detection. The function uses a CRC32 algorithm to detect changes between source and target data. The CRC32 function converts a variable-length string into an 8-character string that is a text representation of the hexadecimal value of a 32 bit-binary sequence. To detect changes between source and target data, use the CRC32 function on the source data and store the output. Then, use the CRC32 function on the target data and compare that output to the output from the source data. The outputs will be the same if the data was not modified, and the outputs will be different if the data was modified.
## Syntax
```
CRC32(string)
```
## Arguments
*string*
A `CHAR` string, a `VARCHAR` string, or an expression that implicitly evaluates to a `CHAR` or `VARCHAR` type.
## Return type
The CRC32 function returns an 8-character string that is a text representation of the hexadecimal value of a 32-bit binary sequence. The Amazon Redshift CRC32 function is based on the CRC-32C polynomial.
## Examples
To show the 8-bit value for the string `Amazon Redshift`.
```
SELECT CRC32('Amazon Redshift');
+----------+
| crc32 |
+----------+
| f2726906 |
+----------+
```
# DIFFERENCE function
The DIFFERENCE function compares the American Soundex codes of two strings. The function returns an `INTEGER` to indicate the number of matching characters between the Soundex codes.
A Soundex code is a string that is four characters long. A Soundex code represents how a word sounds rather than how it is spelled. For example, `Smith` and `Smyth` have the same Soundex code.
## Syntax
```
DIFFERENCE(string1, string2)
```
## Arguments
*string1*
A `CHAR` string, a `VARCHAR` string, or an expression that implicitly evaluates to a `CHAR` or `VARCHAR` type.
*string2*
A `CHAR` string, a `VARCHAR` string, or an expression that implicitly evaluates to a `CHAR` or `VARCHAR` type.
## Return type
INTEGER
The DIFFERENCE function returns an `INTEGER` value from 0–4 that counts the number of matching characters in the American Soundex codes of the two strings. A Soundex code has 4 characters, so the DIFFERENCE function returns `4` when all 4 characters of the strings' American Soundex code values are the same. DIFFERENCE returns `0` if one of the two strings is empty. The function returns `1` if neither string contains valid characters. The DIFFERENCE function converts only English alphabetical lowercase or uppercase ASCII characters, including a–z and A–Z. DIFFERENCE ignores other characters.
## Examples
To compare the Soundex values of the strings `%` and `@`, use the following example. The function returns `1` because neither string contains valid characters.
```
SELECT DIFFERENCE('%', '@');
+------------+
| difference |
+------------+
| 1 |
+------------+
```
To compare the Soundex values of `Amazon` and an empty string, use the following example. The function returns `0` because one of the two strings is empty.
```
SELECT DIFFERENCE('Amazon', '');
+------------+
| difference |
+------------+
| 0 |
+------------+
```
To compare the Soundex values of the strings `Amazon` and `Ama`, use the following example. The function returns `2` because 2 characters of the strings' Soundex values are the same.
```
SELECT DIFFERENCE('Amazon', 'Ama');
+------------+
| difference |
+------------+
| 2 |
+------------+
```
To compare the Soundex values of the strings `Amazon` and `+-*/%Amazon`, use the following example. The function returns `4` because all 4 characters of the strings' Soundex values are the same. Notice that the function ignores the invalid characters `+-*/%` in the second string.
```
SELECT DIFFERENCE('Amazon', '+-*/%Amazon');
+------------+
| difference |
+------------+
| 4 |
+------------+
```
To compare the Soundex values of the strings `AC/DC` and `Ay See Dee See`, use the following example. The function returns `4` because all 4 characters of the strings' Soundex values are the same.
```
SELECT DIFFERENCE('AC/DC', 'Ay See Dee See');
+------------+
| difference |
+------------+
| 4 |
+------------+
```
# INITCAP function
Capitalizes the first letter of each word in a specified string. INITCAP supports UTF-8 multibyte characters, up to a maximum of four bytes per character.
## Syntax
```
INITCAP(string)
```
## Argument
*string*
A `CHAR` string, a `VARCHAR` string, or an expression that implicitly evaluates to a `CHAR` or `VARCHAR` type.
## Return type
VARCHAR
## Usage notes
The INITCAP function makes the first letter of each word in a string uppercase, and any subsequent letters are made (or left) lowercase. Therefore, it is important to understand which characters (other than space characters) function as word separators. A *word separator* character is any non-alphanumeric character, including punctuation marks, symbols, and control characters. All of the following characters are word separators:
```
! " # $ % & ' ( ) * + , - . / : ; < = > ? @ [ \ ] ^ _ ` { | } ~
```
Tabs, newline characters, form feeds, line feeds, and carriage returns are also word separators.
## Examples
The following examples use data from the CATEGORY and USERS tables in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To capitalize the initials of each word in the CATDESC column, use the following example.
```
SELECT catid, catdesc, INITCAP(catdesc)
FROM category
ORDER BY 1, 2, 3;
+-------+--------------------------------------------+--------------------------------------------+
| catid | catdesc | initcap |
+-------+--------------------------------------------+--------------------------------------------+
| 1 | Major League Baseball | Major League Baseball |
| 2 | National Hockey League | National Hockey League |
| 3 | National Football League | National Football League |
| 4 | National Basketball Association | National Basketball Association |
| 5 | Major League Soccer | Major League Soccer |
| 6 | Musical theatre | Musical Theatre |
| 7 | All non-musical theatre | All Non-Musical Theatre |
| 8 | All opera and light opera | All Opera And Light Opera |
| 9 | All rock and pop music concerts | All Rock And Pop Music Concerts |
| 10 | All jazz singers and bands | All Jazz Singers And Bands |
| 11 | All symphony, concerto, and choir concerts | All Symphony, Concerto, And Choir Concerts |
+-------+--------------------------------------------+--------------------------------------------+
```
To show that the INITCAP function does not preserve uppercase characters when they do not begin words, use the following example. For example, the string `MLB` becomes `Mlb`.
```
SELECT INITCAP(catname)
FROM category
ORDER BY catname;
+-----------+
| initcap |
+-----------+
| Classical |
| Jazz |
| Mlb |
| Mls |
| Musicals |
| Nba |
| Nfl |
| Nhl |
| Opera |
| Plays |
| Pop |
+-----------+
```
To show that non-alphanumeric characters other than spaces function as word separators, use the following example. Several letters in each string will be capitalized.
```
SELECT email, INITCAP(email)
FROM users
ORDER BY userid DESC LIMIT 5;
+------------------------------------+------------------------------------+
| email | initcap |
+------------------------------------+------------------------------------+
| urna.Ut@egetdictumplacerat.edu | Urna.Ut@Egetdictumplacerat.Edu |
| nibh.enim@egestas.ca | Nibh.Enim@Egestas.Ca |
| in@Donecat.ca | In@Donecat.Ca |
| sodales@blanditviverraDonec.ca | Sodales@Blanditviverradonec.Ca |
| sociis.natoque.penatibus@vitae.org | Sociis.Natoque.Penatibus@Vitae.Org |
+------------------------------------+------------------------------------+
```
# 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*
A `CHAR` string, a `VARCHAR` string, or any expression that evaluates to a `CHAR` or `VARCHAR` string.
*integer*
A positive integer.
## Return type
VARCHAR
## Examples
The following example uses data from the EVENT table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To return the leftmost 5 and rightmost 5 characters from event names that have event IDs between 1000 and 1005, use the following example.
```
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 |
+---------+----------------+--------+---------+
```
# LEN function
Returns the length of the specified string as the number of characters.
## Syntax
LEN is a synonym of [LENGTH function](r_LENGTH.md), [CHAR\$1LENGTH function](r_CHAR_LENGTH.md), [CHARACTER\$1LENGTH function](r_CHARACTER_LENGTH.md), and [TEXTLEN function](r_TEXTLEN.md).
```
LEN(expression)
```
## Argument
*expression*
A `CHAR` string, a `VARCHAR` string, a `VARBYTE` expression, or an expression that implicitly evaluates to a `CHAR`, `VARCHAR`, or `VARBYTE` type.
## Return type
INTEGER
The LEN function returns an integer indicating the number of characters in the input string.
If the input string is a character string, the LEN function returns the actual number of characters in multi-byte strings, not the number of bytes. For example, a `VARCHAR(12)` column is required to store three four-byte Chinese characters. The LEN function will return `3` for that same string. To get the length of a string in bytes, use the [OCTET\$1LENGTH](r_OCTET_LENGTH.md) function.
## Usage notes
If *expression* is a `CHAR` string, trailing spaces are not counted.
If *expression* is a `VARCHAR` string, trailing spaces are counted.
## Examples
To return the number of bytes and the number of characters in the string `français`, use the following example.
```
SELECT OCTET_LENGTH('français'),
LEN('français');
+--------------+-----+
| octet_length | len |
+--------------+-----+
| 9 | 8 |
+--------------+-----+
```
To return the number of bytes and the number of characters in the string `français` without using the OCTET\$1LENGTH function, use the following example. For more information, see the [CAST function](r_CAST_function.md).
```
SELECT LEN(CAST('français' AS VARBYTE)) as bytes, LEN('français');
+-------+-----+
| bytes | len |
+-------+-----+
| 9 | 8 |
+-------+-----+
```
To return the number of characters in the strings `cat` with no trailing spaces, `cat ` with three trailing spaces, `cat ` with three trailing spaces cast as a `CHAR` of length 6, and `cat ` with three trailing spaces cast as a `VARCHAR` of length 6, use the following example. Notice that the function does not count trailing spaces for `CHAR` strings, but it does count trailing spaces for `VARCHAR` strings.
```
SELECT LEN('cat'), LEN('cat '), LEN(CAST('cat ' AS CHAR(6))) AS len_char, LEN(CAST('cat ' AS VARCHAR(6))) AS len_varchar;
+-----+-----+----------+-------------+
| len | len | len_char | len_varchar |
+-----+-----+----------+-------------+
| 3 | 6 | 3 | 6 |
+-----+-----+----------+-------------+
```
The following example uses data from the VENUE table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To return the 10 longest venue names in the VENUE table, use the following example.
```
SELECT venuename, LEN(venuename)
FROM venue
ORDER BY 2 DESC, 1
LIMIT 10;
+-----------------------------------------+-----+
| venuename | len |
+-----------------------------------------+-----+
| Saratoga Springs Performing Arts Center | 39 |
| Lincoln Center for the Performing Arts | 38 |
| Nassau Veterans Memorial Coliseum | 33 |
| Jacksonville Municipal Stadium | 30 |
| Rangers BallPark in Arlington | 29 |
| University of Phoenix Stadium | 29 |
| Circle in the Square Theatre | 28 |
| Hubert H. Humphrey Metrodome | 28 |
| Oriole Park at Camden Yards | 27 |
| Dick's Sporting Goods Park | 26 |
+-----------------------------------------+-----+
```
# LENGTH function
Synonym of the LEN function.
See [LEN function](r_LEN.md).
# 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*
A `VARCHAR` string or any expression that evaluates to the `VARCHAR` type.
## Return type
string
The LOWER function returns a string that is the same data type as the input string. For example, if the input is a `CHAR` string, the function will return a `CHAR` string.
## Examples
The following example uses data from the CATEGORY table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To convert the `VARCHAR` strings in the CATNAME column to lowercase, use the following example.
```
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 |
+-----------+-----------+
```
# 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 `CHAR` string, a `VARCHAR` string, or an expression that implicitly evaluates to a `CHAR` or `VARCHAR` type.
*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 zero or a negative number, the result of the function is an empty string.
*string2*
(Optional) One or more characters that are prepended or appended to *string1*. If this argument is not specified, spaces are used.
## Return type
VARCHAR
## Examples
The following examples use data from the EVENT table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To truncate a specified set of event names to 20 characters and prepend the shorter names with spaces, use the following example.
```
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 |
+-----------------------+
```
To truncate the same set of event names to 20 characters but append the shorter names with `0123456789`, use the following example.
```
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 |
+----------------------+
```
# 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 does not 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
```
# OCTETINDEX function
The OCTETINDEX function returns the location of a substring within a string as a number of bytes.
## Syntax
```
OCTETINDEX(substring, string)
```
## Arguments
*substring*
A `CHAR` string, a `VARCHAR` string, or an expression that implicitly evaluates to a `CHAR` or `VARCHAR` type.
*string*
A `CHAR` string, a `VARCHAR` string, or an expression that implicitly evaluates to a `CHAR` or `VARCHAR` type.
## Return type
INTEGER
The OCTETINDEX function returns an `INTEGER` value corresponding to the position of the *substring* within the *string* as a number of bytes, where the first character in the *string* is counted as 1. If the *string* doesn't contain multibyte characters, the result is equal to the result of the CHARINDEX function. If the *string* does not contain the *substring*, the function returns `0`. If the *substring* is empty, the function returns `1`.
## Examples
To return the position of the substring `q` in the string `Amazon Redshift`, use the following example. This example returns `0` because the *substring* is not in the *string*.
```
SELECT OCTETINDEX('q', 'Amazon Redshift');
+------------+
| octetindex |
+------------+
| 0 |
+------------+
```
To return the position of an empty substring in the string `Amazon Redshift`, use the following example. This example returns `1` because the *substring* is empty.
```
SELECT OCTETINDEX('', 'Amazon Redshift');
+------------+
| octetindex |
+------------+
| 1 |
+------------+
```
To return the position of the substring `Redshift` in the string `Amazon Redshift`, use the following example. This example returns `8` because the *substring* begins on the eighth byte of the *string*.
```
SELECT OCTETINDEX('Redshift', 'Amazon Redshift');
+------------+
| octetindex |
+------------+
| 8 |
+------------+
```
To return the position of the substring `Redshift` in the string `Amazon Redshift`, use the following example. This example returns `21` because the first six characters of the *string* are double-byte characters.
```
SELECT OCTETINDEX('Redshift', 'Άμαζον Amazon Redshift');
+------------+
| octetindex |
+------------+
| 21 |
+------------+
```
# OCTET\$1LENGTH function
Returns the length of the specified string as the number of bytes.
## Syntax
```
OCTET_LENGTH(expression)
```
## Argument
*expression*
A `CHAR` string, a `VARCHAR` string, a `VARBYTE` expression, or an expression that implicitly evaluates to a `CHAR`, `VARCHAR`, or `VARBYTE` type.
## Return type
INTEGER
The OCTET\$1LENGTH function returns an integer indicating the number of bytes in the input string.
If the input string is a character string, the [LEN](r_LEN.md) function returns the actual number of characters in multi-byte strings, not the number of bytes. For example, a `VARCHAR(12)` column is required to store three four-byte Chinese characters. The OCTET\$1LENGTH function will return `12` for that string, and the LEN function will return `3` for that same string.
## Usage notes
If *expression* is a `CHAR` string, the function returns the length of the `CHAR` string. For example, the output of a `CHAR(6)` input is a `CHAR(6)`.
If *expression* is a `VARCHAR` string, trailing spaces are counted.
## Examples
To return the number of bytes when the string `francais` with three trailing spaces is cast to a `CHAR` and a `VARCHAR` type, use the following example. For more information, see the [CAST function](r_CAST_function.md).
```
SELECT OCTET_LENGTH(CAST('francais ' AS CHAR(15))) AS octet_length_char, OCTET_LENGTH(CAST('francais ' AS VARCHAR(15))) AS octet_length_varchar;
+-------------------+----------------------+
| octet_length_char | octet_length_varchar |
+-------------------+----------------------+
| 15 | 11 |
+-------------------+----------------------+
```
To return the number of bytes and the number of characters in the string `français`, use the following example.
```
SELECT OCTET_LENGTH('français'), LEN('français');
+--------------+-----+
| octet_length | len |
+--------------+-----+
| 9 | 8 |
+--------------+-----+
```
To return the number of bytes when the string `français` is cast as a `VARBYTE`, use the following example.
```
SELECT OCTET_LENGTH(CAST('français' AS VARBYTE));
+--------------+
| octet_length |
+--------------+
| 9 |
+--------------+
```
# POSITION function
Returns the location of the specified substring within a string.
See [CHARINDEX function](r_CHARINDEX.md) and [STRPOS function](r_STRPOS.md) for similar functions.
## 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. POSITION returns `0` if the substring is not found within the string.
## Examples
To return the position of the string `fish` within the word `dog`, use the following example.
```
SELECT POSITION('fish' IN 'dog');
+-----------+
| position |
+-----------+
| 0 |
+-----------+
```
To return the position of the string `fish` within the word `dogfish`, use the following example.
```
SELECT POSITION('fish' IN 'dogfish');
+-----------+
| position |
+-----------+
| 4 |
+-----------+
```
The following example uses the SALES table from the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To return the number of distinct sales transactions with a commission over 999.00 from the SALES table, use the following example. This command counts commissions greater than 999.00 by checking if the decimal is more than 4 places from the beginning of the commission value.
```
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 |
+-----------+-------+
```
# QUOTE\$1IDENT function
The QUOTE\$1IDENT function returns the specified string as a string with a leading double quotation mark and a trailing double quotation mark. The function output can be used as an identifier in a SQL statement. The function appropriately doubles any embedded double quotation marks.
QUOTE\$1IDENT adds double quotation marks only where necessary to create a valid identifier, when the string contains non-identifier characters or would otherwise be folded to lowercase. To always return a single-quoted string, use [QUOTE\$1LITERAL](r_QUOTE_LITERAL.md).
## Syntax
```
QUOTE_IDENT(string)
```
## Argument
*string*
A `CHAR` or `VARCHAR` string.
## Return type
The QUOTE\$1IDENT function returns the same type of string as the input *string*.
## Examples
To return the string `"CAT"` with doubled quotation marks, use the following example.
```
SELECT QUOTE_IDENT('"CAT"');
+-------------+
| quote_ident |
+-------------+
| """CAT""" |
+-------------+
```
The following example uses data from the CATEGORY table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To return the CATNAME column surrounded by quotation marks, use the following example.
```
SELECT catid, QUOTE_IDENT(catname)
FROM category
ORDER BY 1,2;
+-------+-------------+
| catid | quote_ident |
+-------+-------------+
| 1 | "MLB" |
| 2 | "NHL" |
| 3 | "NFL" |
| 4 | "NBA" |
| 5 | "MLS" |
| 6 | "Musicals" |
| 7 | "Plays" |
| 8 | "Opera" |
| 9 | "Pop" |
| 10 | "Jazz" |
| 11 | "Classical" |
+-------+-------------+
```
# QUOTE\$1LITERAL function
The QUOTE\$1LITERAL function returns the specified string as a single quoted string so that it can be used as a string literal in a SQL statement. If the input parameter is a number, QUOTE\$1LITERAL treats it as a string. Appropriately doubles any embedded single quotation marks and backslashes.
## Syntax
```
QUOTE_LITERAL(string)
```
## Argument
*string*
A `CHAR` or `VARCHAR` string.
## Return type
The QUOTE\$1LITERAL function returns a `CHAR` or `VARCHAR` string that is the same data type as the input *string*.
## Examples
To return the string `''CAT''` with SINGLE quotation marks, use the following example.
```
SELECT QUOTE_LITERAL('''CAT''');
+---------------+
| quote_literal |
+---------------+
| '''CAT''' |
+---------------+
```
The following examples use data from the CATEGORY table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To return the CATNAME column surrounded by single quotation marks, use the following example.
```
SELECT catid, QUOTE_LITERAL(catname)
FROM category
ORDER BY 1,2;
+-------+---------------+
| catid | quote_literal |
+-------+---------------+
| 1 | 'MLB' |
| 2 | 'NHL' |
| 3 | 'NFL' |
| 4 | 'NBA' |
| 5 | 'MLS' |
| 6 | 'Musicals' |
| 7 | 'Plays' |
| 8 | 'Opera' |
| 9 | 'Pop' |
| 10 | 'Jazz' |
| 11 | 'Classical' |
+-------+---------------+
```
To return the CATID column surrounded by single quotation marks, use the following example.
```
SELECT QUOTE_LITERAL(catid), catname
FROM category
ORDER BY 1,2;
+---------------+-----------+
| quote_literal | catname |
+---------------+-----------+
| '1' | MLB |
| '10' | Jazz |
| '11' | Classical |
| '2' | NHL |
| '3' | NFL |
| '4' | NBA |
| '5' | MLS |
| '6' | Musicals |
| '7' | Plays |
| '8' | Opera |
| '9' | Pop |
+---------------+-----------+
```
# REGEXP\$1COUNT function
Searches a string for a regular expression pattern and returns an integer that indicates the number of times the specified pattern occurs in the string. If no match is found, then the function returns `0`. For more information about regular expressions, see [POSIX operators](pattern-matching-conditions-posix.md) and [Regular expression](https://en.wikipedia.org/wiki/Regular_expression) in Wikipedia.
## Syntax
```
REGEXP_COUNT( source_string, pattern [, position [, parameters ] ] )
```
## Arguments
*source\$1string*
A `CHAR` or `VARCHAR` string.
*pattern*
A UTF-8 string literal that represents a regular expression pattern. For more information, see [POSIX operators](pattern-matching-conditions-posix.md).
*position*
(Optional) 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*
(Optional) 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. For more information about PCRE, see [Perl Compatible Regular Expressions](https://en.wikipedia.org/wiki/Perl_Compatible_Regular_Expressions) in Wikipedia.
## Return type
INTEGER
## Examples
To count the number of times a three-letter sequence occurs, use the following example.
```
SELECT REGEXP_COUNT('abcdefghijklmnopqrstuvwxyz', '[a-z]{3}');
+--------------+
| regexp_count |
+--------------+
| 8 |
+--------------+
```
To count the occurrences of the string `FOX` using case-insensitive matching, use the following example.
```
SELECT REGEXP_COUNT('the fox', 'FOX', 1, 'i');
+--------------+
| regexp_count |
+--------------+
| 1 |
+--------------+
```
To use a pattern written in the PCRE dialect to locate words containing at least one number and one lowercase letter, use the following example. The example 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 |
+--------------+
```
To use a pattern written in the PCRE dialect to locate words containing at least one number and one lowercase letter, use the following example. 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 |
+--------------+
```
The following example uses data from the USERS table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To count the number of times the top-level domain name is either `org` or `edu`, use the following example.
```
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 |
+-----------------------------------------------+--------------+
```
# 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](r_POSITION.md) function, but lets you search a string for a regular expression pattern. For more information about regular expressions, see [POSIX operators](pattern-matching-conditions-posix.md) and [Regular expression](https://en.wikipedia.org/wiki/Regular_expression) in Wikipedia.
## 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 UTF-8 string literal that represents a regular expression pattern. For more information, see [POSIX operators](pattern-matching-conditions-posix.md).
*position*
(Optional) 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*
(Optional) 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*
(Optional) 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*
(Optional) 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. For more information about PCRE, see [Perl Compatible Regular Expressions](https://en.wikipedia.org/wiki/Perl_Compatible_Regular_Expressions) in Wikipedia.
## Return type
Integer
## Examples
The following examples use data from the USERS table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To search for the `@` character that begins a domain name and returns the starting position of the first match, use the following example.
```
SELECT email, REGEXP_INSTR(email, '@[^.]*')
FROM users
ORDER BY userid LIMIT 4;
+-----------------------------------------------+--------------+
| email | regexp_instr |
+-----------------------------------------------+--------------+
| Etiam.laoreet.libero@sodalesMaurisblandit.edu | 21 |
| Suspendisse.tristique@nonnisiAenean.edu | 22 |
| amet.faucibus.ut@condimentumegetvolutpat.ca | 17 |
| sed@lacusUtnec.ca | 4 |
+-----------------------------------------------+--------------+
```
To search for variants of the word `Center` and returns the starting position of the first match, use the following example.
```
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 |
+-----------------------+--------------+
```
To find the starting position of the first occurrence of the string `FOX`, using case-insensitive matching logic, use the following example.
```
SELECT REGEXP_INSTR('the fox', 'FOX', 1, 1, 0, 'i');
+--------------+
| regexp_instr |
+--------------+
| 5 |
+--------------+
```
To use a pattern written in PCRE dialect to locate words containing at least one number and one lowercase letter, use the following example. 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 |
+--------------+
```
To use a pattern written in PCRE dialect to locate words containing at least one number and one lowercase letter, use the following example. 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](r_REPLACE.md), but lets you search a string for a regular expression pattern. For more information about regular expressions, see [POSIX operators](pattern-matching-conditions-posix.md) and [Regular expression](https://en.wikipedia.org/wiki/Regular_expression) in Wikipedia.
REGEXP\$1REPLACE is similar to the [TRANSLATE function](r_TRANSLATE.md) and the [REPLACE function](r_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 `CHAR` or `VARCHAR` string expression, such as a column name, to be searched.
*pattern*
A UTF-8 string literal that represents a regular expression pattern. For more information, see [POSIX operators](pattern-matching-conditions-posix.md).
*replace\$1string*
(Optional) A `CHAR` or `VARCHAR` string expression, such as a column name, that will replace each occurrence of pattern. The default is an empty string ( "" ).
*position*
(Optional) 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*
(Optional) 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. For more information about PCRE, see [Perl Compatible Regular Expressions](https://en.wikipedia.org/wiki/Perl_Compatible_Regular_Expressions) in Wikipedia.
## Return type
VARCHAR
If either *pattern* or *replace\$1string* is `NULL`, the function returns `NULL`.
## Examples
To replace all occurrences of the string `FOX` within the value `quick brown fox` using case-insensitive matching, use the following example.
```
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. To replace each occurrence of such a word with the value `[hidden]`, use the following example.
```
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. To replace each occurrence of such a word with the value `[hidden]`, but differs from the previous example in that it uses case-insensitive matching, use the following example.
```
SELECT REGEXP_REPLACE('passwd7 plain A1234 a1234', '(?=[^ ]*[a-z])(?=[^ ]*[0-9])[^ ]+', '[hidden]', 1, 'ip');
+----------------------------------+
| regexp_replace |
+----------------------------------+
| [hidden] plain [hidden] [hidden] |
+----------------------------------+
```
The following examples use data from the USERS table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To delete the `@` and domain name from email addresses, use the following example.
```
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 |
+-----------------------------------------------+-----------------------+
```
To replace the domain names of email addresses with `internal.company.com`, use the following example.
```
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 |
+-----------------------------------------------+--------------------------------------------+
```
# REGEXP\$1SUBSTR function
Returns characters from a string by searching it for a regular expression pattern. REGEXP\$1SUBSTR is similar to the [SUBSTRING function](r_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. For more information about regular expressions, see [POSIX operators](pattern-matching-conditions-posix.md) and [Regular expression](https://en.wikipedia.org/wiki/Regular_expression) in Wikipedia.
## Syntax
```
REGEXP_SUBSTR( source_string, pattern [, position [, occurrence [, parameters ] ] ] )
```
## Arguments
*source\$1string*
A string expression to be searched.
*pattern*
A UTF-8 string literal that represents a regular expression pattern. For more information, see [POSIX operators](pattern-matching-conditions-posix.md).
*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 empty.
*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. For more information about PCRE, see [Perl Compatible Regular Expressions](https://en.wikipedia.org/wiki/Perl_Compatible_Regular_Expressions) in Wikipedia.
## Return type
VARCHAR
## Examples
The following example returns the portion of an email address between the @ character and the domain extension. The `users` data queried is from the Amazon Redshift sample data. For more information, see [Sample database](c_sampledb.md).
```
SELECT email, regexp_substr(email,'@[^.]*')
FROM users
ORDER BY userid LIMIT 4;
email | regexp_substr
-----------------------------------------------+--------------------------
Suspendisse.tristique@nonnisiAenean.edu | @nonnisiAenean
amet.faucibus.ut@condimentumegetvolutpat.ca | @condimentumegetvolutpat
sed@lacusUtnec.ca | @lacusUtnec
Cum@accumsan.com | @accumsan
```
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 portion of the input corresponding to the second occurrence of the string `FOX` using case-insensitive matching. The result is empty (non-null, length 0) because there is no second occurrence.
```
SELECT regexp_substr('the fox', 'FOX', 1, 2, 'i');
regexp_substr
---------------
```
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.
Synonym for [REPLICATE function](r_REPLICATE.md).
## 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
VARCHAR
## Examples
The following example uses data from the CATEGORY table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To repeat the value of the CATID column in the CATEGORY table three times, use the following example.
```
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 |
+-------+--------+
```
The following example demonstrates generating strings up to 16,000,000 bytes:
```
SELECT
LEN(REPEAT('X', 5000000)) AS five_million_bytes,
LEN(REPEAT('Y', 16000000)) AS sixteen_million_bytes;
five_million_bytes | sixteen_million_bytes
----------+-----------
5000000 | 16000000
```
# 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](r_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(string, 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 uses data from the CATEGORY table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To convert the string `Shows` to `Theatre` in the CATGROUP field, use the following example.
```
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 |
+-------+----------+----------+
```
# REPLICATE function
Synonym for the REPEAT function.
See [REPEAT function](r_REPEAT.md).
# 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 `VARCHAR` strings. Trailing blanks in `CHAR` strings are ignored.
## Return type
VARCHAR
## Examples
The following examples use data from the USERS and SALES tables in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To select five distinct city names and their corresponding reversed names from the USERS table, use the following example.
```
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 |
+----------+----------+
```
To select five sales IDs and their corresponding reversed IDs cast as character strings, use the following example.
```
SELECT salesid, REVERSE(salesid)
FROM sales
ORDER BY salesid DESC LIMIT 5;
+---------+---------+
| salesid | reverse |
+---------+---------+
| 172456 | 654271 |
| 172455 | 554271 |
| 172454 | 454271 |
| 172453 | 354271 |
| 172452 | 254271 |
+---------+---------+
```
# 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 does not 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.
# SOUNDEX function
The SOUNDEX function returns the American Soundex value consisting of the first letter of the input string followed by a 3–digit encoding of the sounds that represent the English pronunciation of the string that you specify. For example, `Smith` and `Smyth` have the same Soundex value.
## Syntax
```
SOUNDEX(string)
```
## Arguments
*string*
You specify a `CHAR` or `VARCHAR` string that you want to convert to an American Soundex code value.
## Return type
VARCHAR(4)
## Usage notes
The SOUNDEX function converts only English alphabetical lowercase and uppercase ASCII characters, including a–z and A–Z. SOUNDEX ignores other characters. SOUNDEX returns a single Soundex value for a string of multiple words separated by spaces.
```
SELECT SOUNDEX('AWS Amazon');
+---------+
| soundex |
+---------+
| A252 |
+---------+
```
SOUNDEX returns an empty string if the input string doesn't contain any English letters.
```
SELECT SOUNDEX('+-*/%');
+---------+
| soundex |
+---------+
| |
+---------+
```
## Examples
To return the Soundex value for `Amazon`, use the following example.
```
SELECT SOUNDEX('Amazon');
+---------+
| soundex |
+---------+
| A525 |
+---------+
```
To return the Soundex value for `smith` and `smyth`, use the following example. Note that the Soundex values are the same.
```
SELECT SOUNDEX('smith'), SOUNDEX('smyth');
+-------+-------+
| smith | smyth |
+-------+-------+
| S530 | S530 |
+-------+-------+
```
# 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
```
# STRPOS function
Returns the position of a substring within a specified string.
See [CHARINDEX function](r_CHARINDEX.md) and [POSITION function](r_POSITION.md) for similar functions.
## Syntax
```
STRPOS(string, substring )
```
## Arguments
*string*
The first input parameter is the `CHAR` or `VARCHAR` string to be searched.
*substring*
The second parameter is the substring to search for within the *string*.
## Return type
INTEGER
The STRPOS 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
STRPOS returns `0` if the *substring* is not found within the *string*.
```
SELECT STRPOS('dogfish', 'fist');
+--------+
| strpos |
+--------+
| 0 |
+--------+
```
## Examples
To show the position of `fish` within `dogfish`, use the following example.
```
SELECT STRPOS('dogfish', 'fish');
+--------+
| strpos |
+--------+
| 4 |
+--------+
```
The following example uses data from the SALES table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To return the number of sales transactions with a COMMISSION over 999.00 from the SALES table, use the following example.
```
SELECT DISTINCT STRPOS(commission, '.'),
COUNT (STRPOS(commission, '.'))
FROM sales
WHERE STRPOS(commission, '.') > 4
GROUP BY STRPOS(commission, '.')
ORDER BY 1, 2;
+--------+-------+
| strpos | count |
+--------+-------+
| 5 | 629 |
+--------+-------+
```
# STRTOL function
Converts a string expression of a number of the specified base to the equivalent integer value. The converted value must be within the signed 64-bit range.
## Syntax
```
STRTOL(num_string, base)
```
## Arguments
*num\$1string*
String expression of a number to be converted. If *num\$1string* is empty ( `''` ) or begins with the null character (`'\0'`), the converted value is `0`. If *num\$1string* is a column containing a NULL value, STRTOL returns `NULL`. The string can begin with any amount of white space, optionally followed by a single plus '`+`' or minus '`-`' sign to indicate positive or negative. The default is '`+`'. If *base* is `16`, the string can optionally begin with '`0x`'.
*base*
`INTEGER` between 2 and 36.
## Return type
BIGINT
If *num\$1string* is null, the function returns `NULL`.
## Examples
To convert string and base value pairs to integers, use the following examples.
```
SELECT STRTOL('0xf',16);
+--------+
| strtol |
+--------+
| 15 |
+--------+
SELECT STRTOL('abcd1234',16);
+------------+
| strtol |
+------------+
| 2882343476 |
+------------+
SELECT STRTOL('1234567', 10);
+---------+
| strtol |
+---------+
| 1234567 |
+---------+
SELECT STRTOL('1234567', 8);
+--------+
| strtol |
+--------+
| 342391 |
+--------+
SELECT STRTOL('110101', 2);
+--------+
| strtol |
+--------+
| 53 |
+--------+
SELECT STRTOL('\0', 2);
+--------+
| strtol |
+--------+
| 0 |
+--------+
```
# 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(character_string FROM start_position [ FOR number_characters ] )
```
```
SUBSTRING(character_string, start_position, number_characters )
```
```
SUBSTRING(binary_expression, start_byte, number_bytes )
```
```
SUBSTRING(binary_expression, start_byte )
```
## Arguments
*character\$1string*
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.
*number\$1characters*
The number of characters to extract (the length of the substring). The *number\$1characters* is based on the number of characters, not bytes, so that multi-byte characters are counted as single characters. This number cannot be negative.
*binary\$1expression*
The binary\$1expression of data type VARBYTE to be searched.
*start\$1byte*
The position within the binary expression to begin the extraction, starting at 1. This number can be negative.
*number\$1bytes*
The number of bytes to extract, that is, the length of the substring. This number can't be negative.
## Return type
VARCHAR or VARBYTE depending on the input.
## Usage Notes
Following are some examples of how you can use *start\$1position* and *number\$1characters* to extract substrings from various positions in a string.
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 *number\$1characters* 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 `number_characters` -1. For example:
```
select substring('caterpillar',-2,6);
substring
-----------
cat
(1 row)
```
If `start_position` \$1 `number_characters` -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 shows a negative start position of a binary value `abc`. Because the start position is -3, the substring is extracted from the beginning of the binary value. The result is automatically shown as the hexadecimal representation of the binary substring.
```
select substring('abc'::varbyte, -3);
substring
-----------
616263
```
The following example shows a 1 for the start position of a binary value `abc`. Because because there is no length specified, the string is extracted from the start position to the end of the string. The result is automatically shown as the hexadecimal representation of the binary substring.
```
select substring('abc'::varbyte, 1);
substring
-----------
616263
```
The following example shows a 3 for the start position of a binary value `abc`. Because because there is no length specified, the string is extracted from the start position to the end of the string. The result is automatically shown as the hexadecimal representation of the binary substring.
```
select substring('abc'::varbyte, 3);
substring
-----------
63
```
The following example shows a 2 for the start position of a binary value `abc`. The string is extracted from the start position to position 10, but the end of the string is at position 3. The result is automatically shown as the hexadecimal representation of the binary substring.
```
select substring('abc'::varbyte, 2, 10);
substring
-----------
6263
```
The following example shows a 2 for the start position of a binary value `abc`. The string is extracted from the start position for 1 byte. The result is automatically shown as the hexadecimal representation of the binary substring.
```
select substring('abc'::varbyte, 2, 1);
substring
-----------
62
```
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
```
# TEXTLEN function
Synonym of LEN function.
See [LEN function](r_LEN.md).
# 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](r_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
To replace several characters in a string, use the following example.
```
SELECT TRANSLATE('mint tea', 'inea', 'osin');
+-----------+
| translate |
+-----------+
| most tin |
+-----------+
```
The following examples use data from the USERS table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To replace the at sign (@) with a period for all values in a column, use the following example.
```
SELECT email, TRANSLATE(email, '@', '.') as obfuscated_email
FROM users LIMIT 10;
+---------------------------------------+---------------------------------------+
| email | obfuscated_email |
+---------------------------------------+---------------------------------------+
| Cum@accumsan.com | Cum.accumsan.com |
| lorem.ipsum@Vestibulumante.com | lorem.ipsum.Vestibulumante.com |
| non.justo.Proin@ametconsectetuer.edu | non.justo.Proin.ametconsectetuer.edu |
| non.ante.bibendum@porttitortellus.org | non.ante.bibendum.porttitortellus.org |
| eros@blanditatnisi.org | eros.blanditatnisi.org |
| augue@Donec.ca | augue.Donec.ca |
| cursus@pedeacurna.edu | cursus.pedeacurna.edu |
| at@Duis.com | at.Duis.com |
| quam@facilisisvitaeorci.ca | quam.facilisisvitaeorci.ca |
| mi.lorem@nunc.edu | mi.lorem.nunc.edu |
+---------------------------------------+---------------------------------------+
```
To replace spaces with underscores and strips out periods for all values in a column, use the following example.
```
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 blanks or specified characters.
## Syntax
```
TRIM( [ BOTH | LEADING | TRAILING ] [trim_chars FROM ] string )
```
## Arguments
BOTH \$1 LEADING \$1 TRAILING
(Optional) Specifies where to trim characters from. Use BOTH to remove leading and trailing characters, use LEADING to remove leading characters only, and use TRAILING to remove trailing characters only. If this parameter is omitted, both leading and trailing characters are trimmed.
*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, Amazon Redshift implicitly converts the results to `VARCHAR`. If you use the TRIM function in the SELECT list for a SQL function, Amazon Redshift 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](r_CAST_function.md) and [CONVERT function](r_CONVERT_function.md) functions for information about explicit conversions.
## Examples
To trim both leading and trailing blanks from the string ` dog `, use the following example.
```
SELECT TRIM(' dog ');
+-------+
| btrim |
+-------+
| dog |
+-------+
```
To trim both leading and trailing blanks from the string ` dog `, use the following example.
```
SELECT TRIM(BOTH FROM ' dog ');
+-------+
| btrim |
+-------+
| dog |
+-------+
```
To remove the leading double quotation marks from the string `"dog"`, use the following example.
```
SELECT TRIM(LEADING '"' FROM'"dog"');
+-------+
| ltrim |
+-------+
| dog" |
+-------+
```
To remove the trailing double quotation marks from the string `"dog"`, use the following example.
```
SELECT TRIM(TRAILING '"' FROM'"dog"');
+-------+
| rtrim |
+-------+
| "dog |
+-------+
```
TRIM removes any of the characters in *trim\$1chars* when they appear at the beginning or end of *string*. The following example trims the characters 'C', 'D', and 'G' when they appear at the beginning or end of VENUENAME, which is a `VARCHAR` column. For more information, see [Sample database](c_sampledb.md).
```
SELECT venueid, venuename, TRIM('CDG' FROM venuename)
FROM venue
WHERE venuename LIKE '%Park'
ORDER BY 2
LIMIT 7;
+---------+----------------------------+---------------------------+
| venueid | venuename | btrim |
+---------+----------------------------+---------------------------+
| 121 | AT&T Park | AT&T 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. For example, the function will return a `VARCHAR` string if the input is a `VARCHAR` string.
## Examples
The following example uses data from the CATEGORY table in the TICKIT sample database. For more information, see [Sample database](c_sampledb.md).
To convert the CATNAME field to uppercase, use the following.
```
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 |
+-----------+-----------+
```