CREATE TABLE #Products (ID VARCHAR(100))

INSERT INTO #Products
SELECT '1/2CT-RDHL-FGI1-AMD7'
UNION ALL SELECT '1/2CT-RDHL-FGI1-AMD8'
UNION ALL SELECT '1/2-FG-I1-ATT'
UNION ALL SELECT '1/2PR-RNG-ATT-10'
UNION ALL SELECT '1/2PR-RNG-ATT-5'
UNION ALL SELECT '1/2PR-RNG-ATT-6'
UNION ALL SELECT '1/2PR-RNG-ATT-7'
UNION ALL SELECT '1/2PR-RNG-ATT-8'
UNION ALL SELECT '1/2PR-RNG-ATT-9'
UNION ALL SELECT '1/2RDSL-HISI2-AMD-5'
UNION ALL SELECT '1/2RDSL-HISI2-AMD-6'
UNION ALL SELECT '1CT-LDB-SRNG/10'
UNION ALL SELECT '1CT-LDB-SRNG/11'
UNION ALL SELECT '1CT-LDB-SRNG/5'
UNION ALL SELECT '1CT-LDB-SRNG/6'
UNION ALL SELECT '1CT-LDB-SRNG/7'
UNION ALL SELECT '1CT-LDB-SRNG/8'
UNION ALL SELECT '1CT-LDB-SRNG/9'

SELECT [NewID]
FROM #Products
CROSS APPLY (
    SELECT LEFT(ID, 1+LEN(ID)-PATINDEX('%[A-Za-z]%', REVERSE(ID)))) a([NewID])
GROUP BY [NewID]

DROP TABLE #Products