当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Allow parenthesis in postgreSQL regular expression text

问题描述:

These sentences work

SELECT (regexp_matches('Euroschinus Hoff+300'::text, E'(Euroschinus Hoff[\+])([0- 9]+)'::text)::text[])[1]::text as counter

select array_scientificname from simple_cal where array_scientificname ~ 'Semecarpus'

But, if there are some parenthesis, never mind where in the text, both don't work

SELECT (regexp_matches('Euroschinus (testing) Hoff+300'::text, E'(Euroschinus (testing) Hoff[\+])([0-9]+)'::text)::text[])[1]::text as counter

select array_scientificname from simple_cal where array_scientificname ~ 'Semecarpus(test)'

I just want to get, the text. There is no defined pattern for () , can be anywhere on the text.

I noticed that using \ before parenthesis it does the trick (see below), but this is not practical at all. I think i should include somewhere that () are allowed in the string...

SELECT (regexp_matches('Euroschinus (testing) Hoff+300'::text, E'(Euroschinus jaffrei \\(testing\\) Hoff[\+])([0-9]+)'::text)::text[])[1]::text as counter

网友答案:

This doesn't return anything:

SELECT (regexp_matches(
         'Euroschinus (testing) Hoff+300'::text
      ,E'(Euroschinus jaffrei \\(testing\\) Hoff[\\+])([0-9]+)')::text[])[1]::text

This would, after removing the string jaffrei from the pattern:

SELECT (regexp_matches(
         'Euroschinus (testing) Hoff+300'::text
      ,E'(Euroschinus \\(testing\\) Hoff[\\+])([0-9]+)')::text[])[1]::text

Simplify the regexp, loose the pointless character class:

SELECT (regexp_matches(
         'Euroschinus (testing) Hoff+300'::text
      ,E'(Euroschinus \\(testing\\) Hoff\\+)([0-9]+)')::text[])[1]::text

If you are bothered by having to add backslashes, try the setting standard_conforming_strings (Default since PostgreSQL 9.1) and use a plain string instead of a Posix escape sequence:

SELECT (regexp_matches(
         'Euroschinus (testing) Hoff+300'::text
       ,'(Euroschinus \(testing\) Hoff\+)([0-9]+)')::text[])[1]::text

But if you are only interested in the first hit, you'd rather use substring() to begin with. Brackets pick the string you want:

SELECT substring('Euroschinus (testing) Hoff+300'
              , '(Euroschinus \(testing\) Hoff\+)[0-9]+')

Finally, if you are bothered by the mere existence of () in the string (??), remove them:

SELECT substring(translate('Euroschinus (testing) Hoff+300', '()', '')
                        , '(Euroschinus testing Hoff\+)[0-9]+')
分享给朋友:
您可能感兴趣的文章:
随机阅读: