Photoshop Question posted in
The official user guide can be found here.

Split word phrases and give out all subgroups in Postgres – Photoshop

I have a table item name which is like this :

Microsoft Word
Adobe Premiere
Paint
Mozila Firefox
Adobe Photoshop CS7
Windows Movie Maker

I want to select the data (table product, column name) become like this :

Microsoft
Word
Microsoft Word
Adobe
PremiereF
Adobe Premier
Paint
Mozila firefox
Adobe 
Photoshop
CS7
Adobe Photoshop
Photoshop CS7
Windows
Movie
Maker

I’m using Postgres…. is it possible to make like that?

Tags:

2

Answers


  1. 0

    You could use regexp_split_to_array:

    CREATE TABLE s(c TEXT);
INSERT INTO s(c) VALUES('Microsoft Word'), ('Adobe Premiere');

SELECT unnest(regexp_split_to_array(s.c, 's+'))
FROM s
UNION ALL
SELECT c
FROM s;

    Rextester Demo

    EDIT:

    To get every combination you could use:

    WITH src AS (
    SELECT id,name, rn::int, (MAX(rn) OVER(PARTITION BY id))::int AS m_rn
    FROM s, 
     unnest(regexp_split_to_array(s.c, 's+')) WITH ORDINALITY AS sub(name,rn)
)
SELECT id, string_agg(b.Name ,' ' ORDER BY rn) AS combination
FROM (SELECT p.id, p.Name, p.rn, RIGHT(o.n::bit(16)::text, m_rn) AS bitmap
      FROM src AS p
      CROSS JOIN generate_series(1, 100000) AS o(n)     
      WHERE o.n < 2 ^ m_rn) b
WHERE SUBSTRING(b.bitmap, b.rn, 1) = '1'
GROUP BY b.id, b.bitmap
ORDER BY id, b.bitmap;

    Rextester Demo 2

    Login or Signup to reply.
  2. 0

    db<>fiddle

    It is not really clear to me what’s your expected result.

    For Adobe Photoshop CS7 your results are:

    Adobe 
Photoshop
CS7
Adobe Photoshop
Photoshop CS7

    What about the origin string Adobe Photoshop CS7? For the solution I am expecting you are wanting all subphrase in the right order. So the solution should include theAdobe Photoshop CS7result. This is indicated by your other results which include the origin strings.

    (1) First step: get all subphrases from beginning:

    String: A B C D E

A
A B
A B C
A B C D
A B C D E

    Query

    WITH single_words AS (
    SELECT *, row_number() OVER (PARTITION BY id) AS nth_word FROM (         -- B
        SELECT id, regexp_split_to_table(phrase, 's') as word FROM phrases  -- A
    )s
)
SELECT 
    array_agg(word) OVER (PARTITION BY id ORDER BY nth_word) as phrase_part  -- C
FROM single_words;

    A: The WITH query makes the query simpler for writing the same subquery only one time (used in (2)). The regexp_split_to_table function splits the string at whitespaces and puts each word into a single line.

    B: The window function row_number adds a counter to the words which indicates the origin position within the original string (https://www.postgresql.org/docs/current/static/tutorial-window.html).

    C: The window function array_agg() OVER (... ORDER BY nth_word) aggregates the words into a list. The ORDER BY is used to get an ascending word list indicated by the original word position (without the ORDER BY the array_agg would add all words of the phrase getting the original string for all word rows)

    (2) Second step: get all subphrases from all starting points:

    String: A B C D E

A
B
C
D
E
A B
B C
C D
D E
A B C
B C D
C D E
A B C D
B C D E
A B C D E

    Query

    WITH single_words AS (                                                    -- A
    SELECT *, row_number() OVER (PARTITION BY id) AS nth_word FROM (
        SELECT id, regexp_split_to_table(phrase, 's') as word FROM phrases
    )s
)
SELECT 
   *, 
   array_agg(b.word) OVER (PARTITION BY a.id, a.nth_word ORDER BY a.id, a.nth_word, b.nth_word) as phrase_part -- C
FROM single_words a                                                       -- B
JOIN single_words b                                       
ON (a.id = b.id AND a.nth_word <= b.nth_word)

    A: Same as in (1)

    B: cross join the phrases with themself; better speaking: join on word with each following of the same phrase

    C: this window function aggregates the phrase words to the given result.

    If you don’t like the array you could convert the result into a string with the function array_to_string(phrase_part, ' ')

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search