Replace string from column using REPLACE function in SQL Server

Last Reply on Mar 28, 2018 06:03 AM By kalpesh

Posted on Mar 28, 2018 05:14 AM

Hai all,

by using below reference , i am modified my requirement

https://www.aspforums.net/Threads/166816/How-to-split-and-retrieve-value-using-sql-query/

in my table i have one column and rows below like ,

Table Name: TmpDtls Column names are, Id int

Data_Dtls nvarchar(500)

the values are,

 

ID    Data_Dtls
1     ASP-0001-180001
2     ASP-0001-180001,ASP-0001-180002
3     ASP-0001-180001,ASP-0001-180002,ASP-0001-180003
4     ASP-0001-180009  
5     ASP-0001-180006,ASP-0001-180008,ASP-0001-1800015,ASP-0001-1800020

if i select Id, Data_Dtls From table , i wnat to retrieve Data_Dtls from above table i want data below like this, 

ID Data_Dtls
1   0001-180001
2   0001-180001,0001-180002
3   0001-180001,0001-180002,0001-180003
4   0001-180009
5   0001-180006,0001-180008,0001-1800015,0001-1800020

 

Posted on Mar 28, 2018 06:03 AM Modified on on Mar 28, 2018 06:06 AM

Then you just need to use REPLACE like below.

SQL

DECLARE  @Data  TABLE(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' 
     
 
SELECT ID,  REPLACE(Data_Dtls,'ASP-','')  Data_Dtls
FROM @Data 

Output

ID Data_Dtls
1 0001-180001
2 0001-180001,0001-180002
3 0001-180001,0001-180002,0001-180003
4 0001-180009
5 0001-180006,0001-180008,0001-1800015,0001-1800020