From: | Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> |
---|---|
To: | postgre <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Guidance needed on an alternative take on common prefix SQL |
Date: | 2019-08-06 23:25:09 |
Message-ID: | XwheTGk8js0Qqp7RkhH0x_X-rOHSjKPobxXliWeDyyAnYMPSshnUotu9GJ8RZcCjGQY7FDWf0oS8JIi0HYbnBhwGNG2igO2-yHUM-TjQ8SA=@protonmail.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I've seen various Postgres examples here and elsewhere that deal with the old common-prefix problem (i.e. "given 1234 show me the longest match").
I'm in need of a bit of guidance on how best to implement an alternative take. Frankly I don't quite know where to start but I'm guessing it will probably involve CTEs, which is an area I'm very weak on.
So, without further ado, here's the scenario:
Given an SQL filtering query output that includes the following column:
87973891
87973970
87973971
87973972
87973973
87973975
87973976
87973977
87973978
87973979
8797400
The final output should be further filtered down to:
87973891
8797397
8797400
i.e. if $last_digit is present 0–9 inclusive, recursively filter until the remaining string is all the same (i.e. in this case, when $last_digit[0-9] is removed, 8797397 is the same).
So, coming back to the example above:
8797397[0-9] is present
so the "nearest common" I would be looking for is 8797397 because once [0-9] is removed, the 7 is the same on the preceeding digit.
The other two rows ( 87973891 and 8797400) are left untouched because $last_digit is not present in [0-9].
Hope this question makes sense !
Laura
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Colson | 2019-08-07 01:01:54 | Re: Guidance needed on an alternative take on common prefix SQL |
Previous Message | Bryn Llewellyn | 2019-08-06 22:18:51 | Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure? |