0

I have a field name called question and value in that field is How do you think of ${question_1}?

I want to write a query to select this value and I think it's going to use regular expression to do it. Below is the query but it didn't get the result, anyone can help me? Thanks!

select question
from "<table_name>"
where question regexp '.*\s[${].*\s[}?].*';
xmz
  • 151
  • 4
  • 14

1 Answers1

1

Your regular expression appears incorrect here. Keep in mind that pattern matchers such as .* and .+ are greedy and will match more character types than you may have intended. Also, the [${] and [}?] bracket expressions in your regex is indicating match either $ or {, and } or ? respectively, which is not what you are intending to perform.

If your goal is simply to find all text that conforms to the below form specifically:

Does every question end with a question-mark immediately after the ${variable}?

Then a fitting pattern for it in Snowflake SQL could be:

select question
from "<table_name>"
where question regexp '.*\\$\\{[^${}]+\\}\\?$';

Notice the double-backslashing (\\$, \\{, etc.) to disallow treating $ and { as regular expression instructions (to treat them as simple characters), and the use of an inverted bracket expression [^${}] which will accept anything except the characters $, {, or } once the matcher is searching within a potential variable.

The extra $ instruction at the end is to filter only for text that always ends in a question-mark, and can be dropped if a question-mark does not terminate the string every time in your data.

You can extend this to match (and/or capture) multiple variables too, if more than one variable can be present:

select
   question
  ,regexp_substr(question, '(\\$\\{[^${}]+\\})', 1, 1) variable_1
  ,regexp_substr(question, '(\\$\\{[^${}]+\\})', 1, 2) variable_2
from "<table_name>"
where question regexp '.*(\\$\\{[^${}]+\\}).*\\?.*'

The above will match also for question strings such as:

Does this question ${carry} two ${variables}?

I recommend building and understanding your own regular expression pattern interactively with a list of questions to test against, on websites like Regex101, RegExr, etc. that make it easier.