PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Matches the same two digits that were captured in the first group

select *
from bha b 
where b.bks similar to 'd{2}[A-Z](d{2})d\1';

The purpose is to regex but bks is in the form
12A56856
12A12812
12A898389

Although I already have this data in my database, the query is still not successful.
I hope you can help

Input:
45A78963
78C79364
14A56356
56K78978
Output:
14A56356
56K78978
Tags:

2

Answers


  1. 0

    I don’t think this was a typo. I think you tried to avoid the error:

    ERROR:  invalid regular expression: invalid backreference number

    Which was caused by your attempt to use a backreference 1 in a SIMILAR TO regex syntax. The message is a bit confusing because Postgres’ implementation of SIMILAR TO simply doesn’t support backreferences, at all – which I think it should say openly instead of sounding like you’re doing them wrong.

    Your addition of another backslash to get a \1 got rid of the error, but that’s because this made the pattern look for a literal backslash followed by a literal 1 in that spot, matching things like '56K7891'.
    @Stefanov‘s fix works because the ~ operator uses POSIX regular expression syntax, which does support backreferences.

    If you need to handle your third example 12A 89 83 89 with two identical pairs of digits separated by more than one digit, add a + (or a * if you also allow zero digits between those pairs):
    demo at db<>fiddle

    select *
from bha b 
where b.bks ~ '^d{2}[A-Z](d{2})d+1$';
    Login or Signup to reply.
  2. 0

    If your data realy is structured as in the data provided then there is no need for regex – it could be done simply using SubStr() function. Just compare your string’s positions 4,5 against positions 7,8. You can add another test checking if the positions are numbers or not:

    WITH      --  S a m p l e    D a t a :
   tbl as 
      ( Select 1 as id, '45A78963' as test_str Union All
        Select 2, '78C79364' Union All
        Select 3, '14A56356' Union All
        Select 4, '56K78978' 
      )

    --    S Q L :
Select    *
From      tbl
Where     SubStr(test_str, 4, 2) = SubStr(test_str, 7, 2)

    /*    R e s u l t : 
        ID TEST_STR
---------- --------
         3 14A56356
         4 56K78978    */
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search