I need to count for each row the number of times the portion of a link beginning with 'https://t.co/' appears in the text of a column named "Tweet_text".

I've done:

SELECT COUNT(REGEXP_CONTAINS('https://t.co/', Tweet_text)) As Cnt
FROM `MyTable` 

But this returns the overall count over the whole table, not the count row by row.

CodePudding user response：

You can try this query:

SELECT ARRAY_LENGTH(REGEXP_EXTRACT_ALL(Tweet_text, 'https://t.co/'))
FROM MyTable

The function REGEXP_CONTAINS only returns the state whether your regular expression was found:

Returns TRUE if value is a partial match for the regular expression, regexp.

If you want to get the count of found substring in your column you have to use REGEXP_EXTRACT_ALL with ARRAY_LENGTH.

You get the count of each row (not a sum) because you don't use a aggregate function (like COUNT) anymore.