Is it possible in SQL to split the numbers by the delimiter and count them?

I have a column in SQL table with a list of items like below: 99987995|M99987995|99987995|. I want to split the items by delimiter(/|) and then count the number of items. for example I will have 3 items from the list i.e 99987995, M99987995, 99987995. Attached is also an image and what the result will look like. For every group in header 2, I want to count the number of items in header 3. Now header3 has some null/empty fields as well.enter image description here

Answer

Try this code:

SELECT [header2] as Header, Cnt as [Number of items] from [table] t
    CROSS APPLY (
        SELECT COUNT( [value]) Cnt FROM STRING_SPLIT( REPLACE( [header3], '|', ','), ',')     
    )x