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 Q Developer image

Your AI Code Assistant

Generate and update README files, create data-flow diagrams, and keep your project fully documented. Built to handle large projects, Amazon Q Developer works alongside you from idea to production code.

Get started free in your IDE

Top comments (1)

Collapse
 
esproc_spl profile image
Judy

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

AWS Q Developer image

Your AI Code Assistant

Automate your code reviews. Catch bugs before your coworkers. Fix security issues in your code. Built to handle large projects, Amazon Q Developer works alongside you from idea to production code.

Get started free in your IDE

👋 Kindness is contagious

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

Okay