Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.

Possible solutions

String

Using length and regexp_replace

This method isn't applicable here because without an anchor, we can't be sure if we're replacing the substring inside of something-space-delimited. As an example, the above replaces the 2 in 329. We can remedy that by using regexp_replace to anchor the substring.

Because we're not splitting on simple spaces (' ') though we could with more complexity, we may also want to accommodate sub-strings of different lengths like in this question. This is why we explicitly include / length('2'). That reduces to a no-op, but if we we're search for something that was longer than one character, it'd be required.

Using ARRAY[]

Splitting into an ARRAY[]

Here we have to subtract one match splits a string into two fragments, and thus the occurrences is one less than the fragment counts: this xyx split on y, produces {'x', 'x'} and we want the length to be 1 corresponding to the occurrences of y.