SO #42079042, PostgreSQL

Language: Layout:

--PostgreSQL 9.5
--'\\' is a delimiter

CREATE TABLE places (id INT PRIMARY KEY, name VARCHAR(255));
INSERT INTO places (id, name) VALUES (1, 'ups store');
INSERT INTO places (id, name) VALUES (2, 'ups store austin');
INSERT INTO places (id, name) VALUES (3, 'ups store chicago');
INSERT INTO places (id, name) VALUES (4, 'ups store bern');
INSERT INTO places (id, name) VALUES (5, 'walmart');
INSERT INTO places (id, name) VALUES (6, 'target');

CREATE TEMPORARY TABLE lengths (number INT PRIMARY KEY);
INSERT INTO lengths (number) VALUES (0);
INSERT INTO lengths (number) VALUES (1);
INSERT INTO lengths (number) VALUES (2);
INSERT INTO lengths (number) VALUES (3);
INSERT INTO lengths (number) VALUES (4);
INSERT INTO lengths (number) VALUES (5);
INSERT INTO lengths (number) VALUES (6);
INSERT INTO lengths (number) VALUES (7);
INSERT INTO lengths (number) VALUES (8);
INSERT INTO lengths (number) VALUES (9);
INSERT INTO lengths (number) VALUES (10);
INSERT INTO lengths (number) VALUES (11);
INSERT INTO lengths (number) VALUES (12);
INSERT INTO lengths (number) VALUES (13);
INSERT INTO lengths (number) VALUES (14);
INSERT INTO lengths (number) VALUES (15);
INSERT INTO lengths (number) VALUES (16);
INSERT INTO lengths (number) VALUES (17);
INSERT INTO lengths (number) VALUES (18);
INSERT INTO lengths (number) VALUES (19);
INSERT INTO lengths (number) VALUES (20);

SELECT
      id,
      SUBSTRING(name, 1, CASE WHEN number = 0 THEN LENGTH(name) ELSE number END) AS prefix
    FROM
     -- Join all places to all possible substring lengths.
     (SELECT *
      FROM places p
      CROSS JOIN lengths l) subq
    -- If number is zero then no prefix match was found elsewhere
    -- (from the question it looked like you wanted to include these)
    WHERE (subq.number = 0 OR
           -- Look for prefix match elsewhere
           EXISTS (SELECT * FROM places p
                   WHERE SUBSTRING(p.name FROM 1 FOR subq.number)
                         = SUBSTRING(subq.name FROM 1 FOR subq.number)
                     AND p.id <> subq.id))
      -- Include as a prefix match if the whole string is being used
      AND (subq.number = LENGTH(name)
           -- Don't include trailing spaces in a prefix
           OR (SUBSTRING(subq.name, subq.number, 1) <> ' '
               -- Only include the longest prefix match 
               AND NOT EXISTS (SELECT * FROM places p 
                               WHERE SUBSTRING(p.name FROM 1 FOR subq.number + 1)
                                     = SUBSTRING(subq.name FROM 1 FOR subq.number + 1)
                                 AND p.id <> subq.id)))
    ORDER BY id;


absolute service time: 0,55 sec