0

I have a column named source_id That has records like

12334xxx45566
33445xxx5566, 12344xxx4456
22487xxx5234, 98776xxx6789, 34455xxx7828

Output I am expecting, is the counts of 'xxx' in each row

1
2
3
Improve this question

1 Answer 1

Reset to default
1

You can use regexp_matches() which returns each match as a row:

select source_id, (select count(*) from regexp_matches(source_id, 'xxx', 'g')) as counts
from the_table;
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

that works!! thank you soo much. However, is it possible to add the source_id and counts. both these columns
select "Source_Id", (select count(*) from regexp_matches("Source_id", 'xxx', 'g')) as counts from table_name
Well, just add source_id to the SELECT list.
Works now perfectly :)
@arshiya you should accept the answer if it resolves your problem
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.