TSQL - Number of times a string repeats within a varchar field value

The trick is to compare the length of the original field, with the length of the field with all instances of the string you are looking for removed.


select

       Original

       , LEN( Original ) as OriginalLen

       , REPLACE( Original, '|', '' ) as OriginalWithoutPipes

       , len( REPLACE( Original, '|', '' ) ) as OriginalWithoutPipesLen

       , LEN( Original ) - len( REPLACE( Original, '|', '' ) ) as NumberOfPipes

from

       (

              select 'asdf|asdf|asdf' as Original

       ) as V1

;