Is this an efficient way to find overlap of two string columns

Question

I have a table with columns name and full name, and am interested in knowing whether one is part of the other (but they are not equal).
Example:

name        |  full name      |  is_subset
-------------------------------------------
john smith  |  john smith     |  false
john smith  |  john h. smith  |  true
john smith  |  alice jones    |  false

I found a method that works using SPLIT and array intersections, but was curious as to whether there is a simpler or more efficient method.

SELECT 
name,
full name,
CARDINALITY(
    (
        ARRAY_INTERSECT(
            SPLIT(name, ' '),
            SPLIT(full name, ' ')
        )
    )
) = CARDINALITY(SPLIT(name, ' ')) AND CARDINALITY(
    SPLIT(full name, ' ')
) > CARDINALITY(SPLIT(name, ' ')) AS is_subset
from t

Pseudo code : (name EXCEPT fullname) = [] AND (fullname EXCEPT name) != [] — MatBailie
– MatBailie, Commented May 15, 2024 at 6:38
Do you care about duplicates? Such as a b being compared to a a b? — MatBailie
– MatBailie, Commented May 15, 2024 at 6:52
Also, should a b be considered a subset of b a, as they're not equal? — MatBailie
– MatBailie, Commented May 15, 2024 at 7:04
@MatBailie no, don't care about duplicates, a b is equal to a a b. And similarly, since order doesn't count, a b equals b a. — Josh Friedlander
– Josh Friedlander, Commented May 15, 2024 at 8:13
Using ARRAY_EXCEPT and comparisons to empty arrays, should be enough then. — MatBailie
– MatBailie, Commented May 15, 2024 at 9:48

MatBailie · Accepted Answer · 2024-05-15 12:12:22Z

2

-- name is a logical subset of fullname
ARRAY_EXCEPT(
   SPLIT(name, ' '),
   SPLIT(fullname, ' ')
)
=
ARRAY []

AND

-- name and fullname are unequal sets
ARRAY_EXCEPT(
   SPLIT(fullname, ' '),
   SPLIT(name, ' ')
)
!=
ARRAY []

The second condition can probably be reduced to...

AND

LENGTH(name) < LENGTH(fullname)

(The shorter LENGTH() version can be "tricked" by certain uses of duplicates.)

edited May 15, 2024 at 12:12

answered May 15, 2024 at 12:06

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is this an efficient way to find overlap of two string columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related