Which Redshift SQL Regex Matches city_name Starting With San or El?

Analyze data by using AWS services.

Answer Correct answer: B — use Redshift's ~ POSIX regex with ^ and (San|El) to filter Sales rows where city_name begins with San or El.

A company has a data warehouse that contains a table that is named Sales. The company stores the table in Amazon Redshift. The table includes a column that is named city_name. The company wants to query the table to find all rows that have a city_name that starts with "San" or "El". Which SQL query will meet this requirement?

Select from Sales where city_name ~ ‘$(San|El)’;
Select from Sales where city_name ~ ‘^(San|El)’; Correct Answer
Select from Sales where city_name ~’$(San&El)’;
Select from Sales where city_name ~ ‘^(San&El)’;

Community Votes

100%

100% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The question tests Redshift POSIX regex anchoring and alternation for a prefix match; the trap is confusing the ^ start anchor with $ or using & as OR.

This DEA-C01 question tests how to write an Amazon Redshift SQL filter that matches city_name values starting with San or El. It establishes that option B is correct because ~ with ^ and (San|El) expresses the intended anchored alternation.

Many learners pick A because it still contains (San|El), but $ is an end anchor and is misplaced before the group, so it does not force the city_name prefix; C and D also wrongly use & for alternation.

Community Discussion (6 comments)

chrispchrisp 👍 7 Selected: B

Regex Patterns for everyone's reference . : Matches any single character. * : Matches zero or more of the preceding element. + : Matches one or more of the preceding element. [abc] : Matches any of the enclosed characters. [^abc] : Matches any character not enclosed. ^ : Matches the start of a string. $ : Matches the end of a string. | : Logical OR operator. (abc) : Matches 'abc' and remembers the match. Answer is B

andrologin 👍 1 Selected: B

Regex patterns: ^ - used to capture the start of the text/string | - used as an OR operator

bakarys 👍 1 Selected: B

B. Select from Sales where city_name ~ ‘^(San|El)’; This query uses a regular expression pattern with the ~ operator. The caret ^ at the beginning of the pattern indicates that the match must start at the beginning of the string. (San|El) matches either “San” or “El”, and * means zero or more of the preceding element. So this query will return all rows where city_name starts with either “San” or “El”.

HunkyBunky 👍 1 Selected: B

B - becuase of regexp

JohnYang 👍 3 Selected: B

^ asserts the position at the start of the string. (San|El) matches either "San" or "El".

tgv 👍 2 Selected: B

~: This operator indicates the use of a regular expression. ^: This symbol signifies the start of the string. (San|El): This pattern matches strings that start with either "San" or "El".

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

Option B uses Redshift's case-sensitive POSIX regex operator ~ against the Sales.city_name column. The caret ^ anchors the match at the start of the string, and the parenthesized group (San|El) uses | as the logical OR, so the pattern targets city_name values whose prefix is San or El. Although a purist regex for prefix matching would omit the trailing * because ^(San|El) already tests the prefix, B is the only supplied option that places the start anchor correctly and uses valid regex alternation. The table and column names are also referenced correctly in the SELECT statement.

Why the Other Options Are Wrong

Option A uses $(San|El)*, but $ is an end-of-string anchor and is misplaced before the group; it does not assert that city_name begins with San or El. Options C and D use & inside the pattern, but & is not the POSIX regex OR operator, so neither query expresses "San or El" as alternatives. Option D also has the ^ start anchor, but its (San&El) group is still invalid for the intended alternation. Therefore none of A, C, or D reliably implements the requested prefix filter.

Community Comment Notes

chrispchrisp supplied a regex reference noting that ^ matches the start of a string and | is the logical OR operator. JohnYang explained that ^ asserts the start position and that (San|El) matches either "San" or "El". tgv and andrologin made the same point about ~ being the regex operator and ^ marking the beginning of the string. bakarys summarized that the pattern returns rows where city_name starts with either San or El, while HunkyBunky selected B simply "becuase of regexp" (sic).

Official Reference

https://docs.aws.amazon.com/redshift/latest/dg/r_POSIX_operators.html

Exam Strategy

For Redshift regex questions, first identify the anchor needed: ^ for a prefix, $ for a suffix, and | for alternation. Eliminate options that use & as OR or place $ before the prefix group, then confirm the query references the correct table and column.

Frequently Asked Questions

Why is option A wrong if it also uses San|El?

In A, $ is an end anchor and is placed before the group, so it does not assert that city_name starts with San or El. Redshift needs ^ at the beginning to enforce a prefix match.

Does the * in ^(San|El)* matter for this Redshift regex?

The exam treats the pattern as an anchored alternation for a San or El prefix; the trailing * is redundant for that intent, while ^ and | are what distinguish option B from A, C, and D.

More DEA-C01 FAQ →

Related Analysis

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide