Here we get away from splitting and instead use the \m, and \M anchors for word-boundaries. SELECT id, xĬROSS JOIN LATERAL regexp_split_to_table(data, ' ')Īnd, from there you can run regular SQL on it. In this method you're using GROUP BY and count(). Using a TABLE Splitting into a table with regexp_split_to_table CROSS JOIN LATERAL string_to_array(data, '2') AS t(x) Īlternatively, we can use string_to_array to separate something that's space-delimited and then count the matches, SELECT id, array_length(array_positions(x, '2'), 1)ĬROSS JOIN LATERAL string_to_array(data, ' ') AS t(x) SELECT array_length(x, 1) - 1ĬROSS JOIN LATERAL regexp_split_to_array(data, '\m2\M') AS t(x) 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 and we want the length to be 1 corresponding to the occurrences of y. SELECT length(data) - length(regexp_replace(data, '\m42\M', '', 'g')) That reduces to a no-op, but if we we're search for something that was longer than one character, it'd be required. This is why we explicitly include / length('2'). SELECT length(data) - length(regexp_replace(data, '\m2\M', '', 'g'))īecause 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. We can remedy that by using regexp_replace to anchor the substring. As an example, the above replaces the 2 in 329. 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. Most RDBMS's provide some method to calculate substring occurrences like this, SELECT length(data) - length(replace(data, '2', '')) Possible solutions String Using length and regexp_replace SLOW Something that converts from a string to a table.Something that converts from a string to an array.FASTEST was the pl/perl method which I placed last on this list because it requires pl/perl, and is likely not needed for most workloads.įAST A string function, such as one the pattern explained below length(str) - length(*replace(str, replaceStr)).
0 kommentar(er)
