DEV Community

Judy
Judy

Posted on

3 1 1 1 1

SQL, split values of two columns respectively and count members of their intersection #eg20

In PostgreSQL database’s table T, items_current_order field and items_next_order field contain comma-separated strings having duplicate characters and enclosed by braces:

Image description
We need to add a computed column named count to the table. Rule: Split items_current_order field and items_next_order field respectively into a set having duplicate members and count members of their intersection.

Image description
Write the following SPL code:

Image description
derive() function adds a computed column to A1’s table. #3 represents the 3rd field; we can also use name to represent the field. split() function splits each string; @c option means using the comma to separate the string. ^ is the operator for computing intersection.

Source:https://stackoverflow.com/questions/78453352/how-to-get-count-values-array1-in-array2

AWS GenAI LIVE image

How is generative AI increasing efficiency?

Join AWS GenAI LIVE! to find out how gen AI is reshaping productivity, streamlining processes, and driving innovation.

Learn more

Top comments (1)

Collapse
 
esproc_spl profile image
Judy

SPL open source address:github.com/SPLWare/esProc/stargazers

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay