Get Single and modified column value using COALESCE and REPLACE in SQL Server

Use COALESCE and REPLACE SQL function. Refer the below sample test query for your reference.

SQL

CREATE TABLE #Data(ID INT,Data_Dtls VARCHAR(MAX))

INSERT INTO #Data
SELECT 1,'ASP-0001-180001'
UNION ALL
SELECT 2,'ASP-0001-180001,ASP-0001-180002'
UNION ALL
SELECT 3,'ASP-0001-180001,ASP-0001-180002,ASP-0001-180003'
UNION ALL
SELECT 4,'ASP-0001-180009'
UNION ALL
SELECT 5,'ASP-0001-180006,ASP-0001-180008,ASP-0001-1800015,ASP-0001-1800020'

    
DECLARE @Data_Dtls VARCHAR(200) 
SELECT @Data_Dtls = COALESCE(@Data_Dtls + ' ', '') + CAST(REPLACE(Data_Dtls,'ASP-','')  AS VARCHAR(MAX))
FROM #Data
      
SELECT @Data_Dtls As CustomeValue

Output