【Question】
I have an author field. Each value is like this: author1, author2, author3. I want to get records containing same authors (it’s not necessarily that all of them are same) but in different orders. For example, “Tom,Jack,Bob” and “Bob,Tom,Jack”.

【Answer】

You can perform string split in SQL. But it’s much easier to handle it in SPL (Structured Process Language). Here’s the SPL script:

A2 splits each author value as a sequence, and sorts and groups the field. Then select groups containing repeated members but in different order.

Suppose data looks like this:

id author

1 Tom, Jack, Bob

2 Jack, Bob, Tom

3 May, Tom, Jack

4 Bob, Jack, Tom

5 aaa,bbb, Tom

6 Tom, May, Jack

Here’s the desired result:

1 Tom, Jack, Bob

2 Jack, Bob, Tom

4 Bob, Jack, Tom

6 Tom, May, Jack