How can I extract two values between square brackets an get the results in two different columns?

To extract two values between square brackets and get the results in two different columns, you can use a combination of string functions and regular expressions.

The specific solution will depend on the SQL engine you are using, but the general idea is to use a function that can extract substrings from a string (e.g. SUBSTRING(), SUBSTR()) and regular expressions to match the values between the brackets.

Here’s an example of how you can extract two values between square brackets and get the results in two different columns using the REGEXP_SUBSTR() function and regular expressions in PostgreSQL:

WITH data (column1) AS (VALUES ('[value1] [value2]'))
SELECT REGEXP_SUBSTR(column1, '\[(.*?)\]', 1, 1) AS value1,
       REGEXP_SUBSTR(column1, '\[(.*?)\]', 1, 2) AS value2
FROM data;

In this example, REGEXP_SUBSTR(column1, '\[(.*?)\]', 1, 1) will extract the first occurrence of a string between brackets and return “value1” and REGEXP_SUBSTR(column1, '\[(.*?)\]', 1, 2) will extract