What is STARTS_WITH Function Alternative in Redshift?

Post author:Vithal S
Post last modified:November 30, 2021
Post category:Redshift
Reading time:5 mins read

The relational databases such as Oracle and cloud databases such as BigQuery, Snowflake supports starts with function. The function name may be different, but the functionality is same. For example, Oracle and BigQuery provides STARTS_WITH function and Snowflake contains STARTSWITH function. The Amazon Redshift does not provide STARTS_WITH function. In my other article, What is ENDS_WITH Function Alternative in Redshift, we have seen ends_with alternative in Redshift. In this article, we will check what is STARTS_WITH function alternative in AWS Redshift?

STARTS_WITH Function In Redshift

Not all the string functions which are available in relational databases such as Oracle are supported in Redshift. One of such a function is STARTS_WITH.

The starts_with function indicates whether or not the source string begins with the search string.

Following is the syntax of starts_with function.

STARTS_WITH( <expr1> , <expr2> )

The function returns TRUE is expr1 starts with expr2. Otherwise, returns FALSE.

There is no equivalent function in Amazon Redshift. However, as a work around you can use LEFT and SUBSTRING string functions.

Redshift LEFT Function as a STARTS_WITH Function Alternative
Redshift SUBSTRING Function as a STARTS_WITH Function Alternative

Now, let us check these alternative function in brief with an example.

Redshift LEFT as a STARTS_WITH Alternative

The Redshift LEFT function return the specified number of leftmost characters from a character string or input string.

For example, consider following Redshift LEFT function to check leftmost character.

WITH items AS
  (SELECT 'foo' as item
  UNION ALL
  SELECT 'bar' as item
  UNION ALL
  SELECT 'baz' as item)
select 
LEFT(item, 1) = 'b' as example
FROM items;
 example
---------
 f
 t
 t
(3 rows)

Redshift SUBSTRING as a STARTS_WITH Alternative

Another easy way is to use substring string function as a starts_with alternative. The SUBSTRING functions returns the characters extracted from a string based on the specified character position for a specified number of characters.

For example, consider following Redshift substring function to check leftmost char.

WITH items AS
  (SELECT 'foo' as item
  UNION ALL
  SELECT 'bar' as item
  UNION ALL
  SELECT 'baz' as item) 
select 
SUBSTRING(item, 1, 1) = 'b' as example2 
FROM items;
 example2
----------
 f
 t
 t
(3 rows)