Commonly used Redshift String Functions and Examples

Amazon Redshift String Functions are used for various string manipulations in your SQL queries. Redshift supports most of the standard SQL string function for your string analysis in Amazon Redshift. Redshift also provides the Redshift extension to the string manipulation functions.

Redshift String Functions

Amazon Redshift provides several string functions that allow you to manipulate and analyze character strings. These Amazon Redshift string processing techniques allow you to handle complex string manipulations.

Following are some of the most commonly used string functions in Amazon Redshift:

Redshift String Function	Description
LEN(expression) / LENGTH(expression) / CHAR_LENGTH(expression) / CHARACTER_LENGTH(expression) / TEXTLEN(expression)	Returns the length of the specified string as the number of characters. You can use any of the mentioned function as per your requirements.
BTRIM(string [, matching_string ] )	Removes leading and trailing space. Also trims string by matching characters
CHARINDEX( substring, string )/ STRPOS(string, substring )	Returns the location of the specified substring within a string. You can use any of the mentioned function as per your requirements.
CHR(number)	The CHR function returns the character that matches the ASCII code point value specified by of the input parameter.
CONCAT ( string1, string2 )	The CONCAT function concatenates two character strings and returns the resulting string.
INITCAP(string)	Capitalizes the first letter of each word in a specified string.
LEFT ( string, integer )	This functions return the specified number of leftmost characters. Read: Redshift LEFT and RIGHT
RIGHT ( string, integer )	This functions return the specified number of rightmost characters. Read: Redshift LEFT and RIGHT
LOWER(string)	Converts string to lower case
UPPER(string)	Converts string to UPPER case
LPAD (string1, length, [ string2 ])	Pads the left side of string with characters to create a new string. This functions prepend characters to a string. Read: Redshift Pad zeros
RPAD (string1, length, [ string2 ])	Pads the right side of string with characters to create a new string. This functions append characters to a string. Read: Redshift Pad zeros
LTRIM(string, ‘trim_chars’)	The LTRIM function trims a specified set of characters from the beginning of a string.
RTRIM(string, ‘trim_chars’)	The RTRIM function trims a specified set of characters from the end of a string.
QUOTE_IDENT(string)	The QUOTE_IDENT function returns the specified string as a double quoted string.
QUOTE_LITERAL(string)	The QUOTE_LITERAL function returns the specified string as a single quoted string.
POSITION(substring IN string )	Returns the first location of the specified substring within a string.
REPEAT(string, integer)/ REPLICATE(string, integer)	Repeats given string the specified number of times.
REPLACE(string1, old_chars, new_chars)	Replace old_chars with new_chars in given string. Read: How to use Amazon Redshift Replace Function?
REVERSE ( expression )	Return given string in revers character order.
SPLIT_PART(string, delimiter, part)	Splits a string on the specified delimiter and returns the part at the specified position.
SUBSTRING(string, start_position, number_characters ) / SUBSTRING(string FROM start_position [ FOR number_characters ] )	Returns the characters extracted from a string based on the specified character position for a specified number of characters. You can use any of the mentioned function as per your requirements.
TRANSLATE ( expression, characters_to_replace, characters_to_substitute )	Replaces any character in string that matches a character in the characters_to_replace set with the corresponding character in the characters_to_substitute set.

These functions can be used in your SQL statements and expressions to manipulate and analyze character strings in your Redshift tables.

Redshift String Functions Examples

Read:

• Amazon Redshift Hash Functions and Examples
• What is STARTS_WITH Function Alternative in Redshift?
• What is ENDS_WITH Function Alternative in Redshift?

Below are some of sample example on Redshift string functions.

Redshift pad zero using String functions:

In Redshift, you can use the lpad or rpad string functions to pad a string with zeros.

training=# select LPAD('1234',8,'0');
 lpad
----------
 00001234
(1 row)

training=# select RPAD('1234',8,'0');
 rpad
----------
 12340000
(1 row)

Redshift SUBSTRING function Examples:

training=# select SUBSTRING ('1234789464361',1,5);
 substring
-----------
 12347
(1 row)

Redshift TRANSLATE function Examples:

training=# select TRANSLATE ('123CDR123','CDR','123');
 translate
-----------
 123123123
(1 row)

Redshift TRIM function Examples:

The TRIM functions such as LTRIM, RTRIM and BTRIM in Amazon Redshift removes specified characters from the beginning and end of a string. It takes two arguments: the input string and the character(s) to remove.

Following example removes the leading and trailing spaces.

training=# select BTRIM (' 1234567891 ');
 btrim
------------
 1234567891
(1 row)

Read:

• Redshift Type Conversion Functions and Examples
• Amazon Redshift Split Delimited Fields into Table Records and Examples
• Redshift Date Functions and Examples
• Redshift Analytic Functions and Examples
• How to Alter Redshift Table column Data type? Explanation

Hope this helps 🙂