By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SomeData(CounterKey varchar(50));
INSERT INTO SomeData(CounterKey)
VALUES('CIVUS0.35V'),
('916148-010-CIVUS'),
('916425-010-CIVUS'),
('CIVUS0.35B'),
('257198-010-CIVUS'),
('916425-010-CIVUS'),
('UCIVUS0.35P'),
('7116-7004-08-UCIVUS'),
('916148-010-UCIVUS'),
('UCIVUS0.35P')
10 rows affected
;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
| 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 |