quiz 3, question 41: Regexp_count function – does not return a list

[paul10101]

question states:
A data engineering team at a company is using Amazon Redshift for its data warehousing needs. There is a table with a column named category. The team needs to create a query that returns a list of data where the category field starts with either “TAD” or “AGO”.

Which of the following Redshift SQL queries would correctly fulfill the team’s requirement?

The correct answer is supposedly
SELECT * FROM data_table WHERE REGEXP_COUNT(category, ‘^TAD’) OR REGEXP_COUNT(category, ‘^AGO’);

However, this function, as the explanation states, “REGEXP_COUNT 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”

It does not return a LIST of data as the requirement of the question asks for. if the requirement was to return a count of each occurrence, that would make more sense.

Am I missing something?

[JR-TutorialsDojo]

Hello paul10101,

Thanks for the feedback.

You’re absolutely right to question that. At first glance, REGEXP_COUNT does sound like it’s returning a count rather than a list. But in this case, it’s being used inside a WHERE clause, which means we’re not interested in the actual count value. We’re just checking whether it’s non-zero (i.e., whether the pattern matches). If it does, the row is included in the result set.

So even though the function name says “COUNT,” the query is still returning a list of rows where the category starts with either “TAD” or “AGO.” You may refer to the given explanation, especially the image attached, since it shows the actual output, which lists the rows where the category starts with either “TAD” or “AGO.” That confirms the query behaves as intended.

I hope this helps! Let us know if you need further assistance.

Best regards,
JR @ Tutorials Dojo