How to check if a string in one field exist in every element of a comma separated field

I have a table which contains two fields. The first is name of type string. The second contains one or more strings separated by comma (but it can contain a single string with no commas at all)

I want to construct a query to know if the string in the name field does not exist in every comma separated strings in the names field.

Example 1:

---------------------------------------------------------
name          names
---------------------------------------------------------
myname        xmyname,myname,mynamey

All the comma separated strings contain the word myname. So the query shoudl not return this row.

But, Example 2:

---------------------------------------------------------
name          names
---------------------------------------------------------
myname        x,myname,mynamey

Should be returned. Because x does not contain myname.

The condition is that, if the string in the field name does not exists in each of the comma separated strings in the names field, then return the row.

This is not correct as this query will not return true in example 2 (which contains x which does not contain myname).

IMPORTANT NOTE:

1) There is not limit of how many commas there. It can be 0 commas or more. How to deal with this?

2) The strings are variables. It is not always the case that the string is myname. Each row contains a different string in the name field.

Answer

Try this regular expression:

where not concat(names, ',') regexp replace('^([^,]*{n}[^,]*,)*$', '{n}', name)

db-fiddle demo

How to read the pattern:

The inner pattern [^,]*{n}[^,]*, means

• Any non comma character [^,] repeated any number of times (* means no times or multiple times).
• followed by the value of the column name ({n} is a placeholder and will be replaced with the actual value using the replace() function)
• followed by any non comma character [^,] repeated any number of times
• followed by a comma

The outer pattern ^({inner_pattern})*$ means

• Start of the string (^)
• followed by the inner pattern repeated any number of times
• followed by end of string ($)

To make this work, a comma is appended to the names column (concat(names, ',')), so that every element in the string ends with a comma.

The pattern will ensure, that any element in the comma separated string contains the value of the name column. Since you want the opposite result, we use where not ...