CODEWITHSUNDEEP
sqlregexpostgresql
mongotop
mongotop

Reputation: 5774

how to use the operator OR in regex for potgresql database

I have an unconventional JSON column in my table: This is the possible values : 

[{"tarif pour 1 :":"$40,00"},{"Réduction :":"-$10,00 "}]

[{"Full Fare For 10 :":"$50.00"},{"Promotion:":"-$20.00"}]

my goal is using postgresql function substring how to get the values

$40,00
$50.00

I could do it but using 2 separate REGEX like:

    SELECT
         , substring(column, '"Full [Ff]are [Ff]or.+?:":"(.+?)"') AS value_1
         , substring(column, '"[Pp]lein [Tt]arif [Pp]our.+?:":"(.+?)"') AS value_2
FROM table

How can I include the both REGEX in my column to show the dollar amount after either or the strings that starts with [Pp]lein [Tt]arif [Pp]our.+?:":" OR [Ff]ull [Ff]are [Ff]or.+?:":"

Upvotes: 0

Views: 32

Answers (1)

klin
klin

Reputation: 121889

Use coalesce():

with a_table(a_column) as (
values
    ('[{"Plein tarif pour 1 :":"$40,00"},{"Réduction :":"-$10,00 "}]'),
    ('[{"Full Fare For 10 :":"$50.00"},{"Promotion:":"-$20.00"}]')
)
select 
    coalesce(
        substring(a_column, '"Full [Ff]are [Ff]or.+?:":"(.+?)"'),
        substring(a_column, '"[Pp]lein [Tt]arif [Pp]our.+?:":"(.+?)"')
    ) as value
from a_table;

 value  
--------
 $40,00
 $50.00
(2 rows)

Upvotes: 1

Related Questions