f you want to count the number of instances of strings with more than a single character, you can either use the previous solution with regex, or this solution uses STRING_SPLIT, which I believe was introduced in SQL Server 2016. Also you’ll need compatibility level 130 and higher.

ALTER DATABASE [database_name] SET COMPATIBILITY_LEVEL = 130

.

--some data DECLARE @table TABLE (col varchar(500)) INSERT INTO @table SELECT ''''whaCHAR(10)teverCHAR(10)whateverCHAR(10)'''' INSERT INTO @table SELECT ''''whaCHAR(10)teverwhateverCHAR(10)'''' INSERT INTO @table SELECT ''''whaCHAR(10)teverCHAR(10)whateverCHAR(10)~''''  --string to find DECLARE @string varchar(100) = ''''CHAR(10)''''  --select SELECT      col   , (SELECT COUNT(*) - 1 FROM STRING_SPLIT (REPLACE(REPLACE(col, ''''~'''', ''''''''), ''''CHAR(10)'''', ''''~''''), ''''~'''')) AS ''''NumberOfBreaks'''' FROM @table

 -- DECLARE field because your table type may be text

DECLARE @mmRxClaim nvarchar(MAX)   -- Getting Value from table SELECT top (1) @mmRxClaim = mRxClaim FROM RxClaim WHERE rxclaimid_PK =362  -- Main String Value SELECT @mmRxClaim AS MainStringValue  -- Count Multiple Character for this number of space will be number of character SELECT LEN(@mmRxClaim) - LEN(REPLACE(@mmRxClaim, ''''GS'''', '''' '''')) AS CountMultipleCharacter  -- Count Single Character for this number of space will be one SELECT LEN(@mmRxClaim) - LEN(REPLACE(@mmRxClaim, ''''G'''', '''''''')) AS CountSingleCharacter