By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tests(example text);
INSERT INTO tests VALUES
('nycTrSeven'),
('nycPkExt'),
('nycAbEchTrn');
SELECT substring(example from '^(([^A-Z]*[A-Z]){0,1}[^A-Z]*)')
FROM tests;
CREATE TABLE
INSERT 0 3
| substring |
|---|
| nycTr |
| nycPk |
| nycAb |
SELECT 3
--https://stackoverflow.com/questions/77045784/sql-split-string-on-second-capital-or-third-letter/77045930#comment135822649_77045784
--JNevill's comment, not entirely correct
SELECT regexp_replace(example,'([A-Z].*)([A-Z].*)', '\1')
FROM tests;
| regexp_replace |
|---|
| nycTr |
| nycPk |
| nycAbEch |
SELECT 3
SELECT left(example, regexp_instr(example,'[A-Z]',1,2) -1 ) FROM tests;
--take left of the example string,
--up to -1 character from where the second capital letter is
| left |
|---|
| nycTr |
| nycPk |
| nycAb |
SELECT 3