What is ENDS_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 ends with function. The function name may be different, but the functionality is same. For example, Oracle and BigQuery provides ENDS_WITH function and Snowflake contains ENDSWITH function. The Amazon Redshift does not provide ENDS_WITH function. In my other article, What is STARTS_WITH Function Alternative in Redshift, we have seen starts_with alternative in Redshift. In this article, we will check what is ENDS_WITH function alternative in AWS Redshift?

ENDS_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 ENDS_WITH.

The ends_with function indicates whether or not the source string ends with the search string.

Following is the syntax of ends_with function.

ENDS_WITH( <expr1> , <expr2> )

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

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

Redshift RIGHT Function as a ENDS_WITH Function Alternative
Redshift SUBSTRING Function as a ENDS_WITH Function Alternative

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

Redshift RIGHT as a ENDS_WITH Alternative

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

For example, consider following Redshift RIGHT function to check rightmost character.

WITH items AS
  (SELECT 'apple' as item
  UNION ALL
  SELECT 'banana' as item
  UNION ALL
  SELECT 'orange' as item) 
SELECT
  RIGHT(item, 1) = 'e' as example 
FROM items;
 example
---------
 t
 f
 t
(3 rows)

Redshift SUBSTRING as a ENDS_WITH Alternative

Another easy way is to use substring string function as a ends_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 rightmost char.

WITH items AS
  (SELECT 'apple' as item
  UNION ALL
  SELECT 'banana' as item
  UNION ALL
  SELECT 'orange' as item) 
SELECT 
  SUBSTRING(item, LENGTH(item), 1)  = 'e' as example 
FROM items;
 example
---------
 t
 f
 t
(3 rows)