You were pretty close to find solution...
;WITH CTE AS
(
SELECT CounterKey,
PARSENAME(CounterKey, 2) AS Part1,
LEFT(PARSENAME(CounterKey, 1), 2) AS Part2,
RIGHT(PARSENAME(CounterKey, 1), 1) AS Part3
FROM SomeData
WHERE CHARINDEX('.', CounterKey)>0
UNION ALL
SELECT CounterKey,
PARSENAME(REPLACE(CounterKey, '-', '.'), 3) AS Part1,
PARSENAME(REPLACE(CounterKey, '-', '.'), 2) AS Part2,
PARSENAME(REPLACE(CounterKey, '-', '.'), 1) AS Part3
FROM SomeData
WHERE CHARINDEX('-', CounterKey)>0
)
SELECT *
FROM CTE
db<>fiddle[
^]
Result:
CounterKey Part1 Part2 Part3
CIVUS0.35V CIVUS0 35 V
CIVUS0.35B CIVUS0 35 B
UCIVUS0.35P UCIVUS0 35 P
UCIVUS0.35P UCIVUS0 35 P
916148-010-CIVUS 916148 010 CIVUS
916425-010-CIVUS 916425 010 CIVUS
257198-010-CIVUS 257198 010 CIVUS
916425-010-CIVUS 916425 010 CIVUS
7116-7004-08-UCIVUS 7004 08 UCIVUS
916148-010-UCIVUS 916148 010 UCIVUS