Get substring from regular expression - everything up to a character

Question

There is a questions table with a column called options which contains

'r>>>>>5####answer1|4####answer2|3####answer3|2####answer4|1####answer5'

Which are pairs of possible answers

• 5 = answer1
• 4 = answer2
• etc.

The questions are set up by users but the pattern is the same.

Another table has a users answer which is the option selected. I'm attempting to write SQL to extract the answer text.

For example 4 should display answer2.

I tried:

SELECT substring(question.options from '%4####@"%@"[\|]%' for '@') AS answertext
FROM ...

But it displays answer2|3####answer3|2####answer4.

How do I get everything up to the first |?

Answer 1

The regular expression supplied by @Abelisto in a comment works nicely. You don't need to escape | in the character class, so it can be:

SELECT substring(options, '4#+([^|]+)' ...

Assuming the number of the answer is limited to a single digit, there is a simple solution without regular expressions, too:

SELECT right(x, -5) AS answer
FROM   unnest( string_to_array(right(
         'r>>>>>5####answer1|4####answer2|3####answer3|2####answer4|1####answer5'
       , -6), '|')) x
WHERE  x LIKE '4%';  -- pick number

Applied to your table:

SELECT right(x, -5) AS answer
FROM   question q, unnest( string_to_array(right(q.options, -6), '|')) x
WHERE  q.question_id = 1
AND    x LIKE '4%';

---

But really, you should normalize your unfortunate design. It's a 1:n design between question and answers. Assuming a PK question.question_id you can fix it quickly like this:

CREATE TABLE answer AS
SELECT q.question_id, left(x,1)::int AS answer_id, right(x, -5) AS answer
FROM   question q, unnest(string_to_array(right(q.options, -6), '|')) x;

ALTER TABLE answer
   ADD PRIMARY KEY (question_id, answer_id)
 , ADD CONSTRAINT q_fk FOREIGN KEY (question_id) REFERENCES question(question_id)
       ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE question DROP column options;

Then your query simply is:

SELECT answer
FROM   answer
WHERE  question_id = 1
AND    answer_id = 4;

Related:

• How to implement a many-to-many relationship in PostgreSQL?