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

nagaraju...

SOLVED CLOSED ACTIVE ANSWERED
User: nagaraju60
Posted: on Mar 16, 2018 05:19 AM
Forum: SQL Server
Answer: 1
Views: 3120

Hai all, 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

whenever i retrieve Data_Dtls from above table i want data below like this,

0001-180001 0001-180001,0001-180002 0001-180001,0001-180002,0001-180003 0001-180009 0001-180006,0001-180008,0001-1800015,0001-1800020

how to acheive this result by using which query.

Download FREE API for Word, Excel and PDF in ASP.Net

kalpesh

0 7

Reply
ANSWER
User: kalpesh
Replied: on Mar 20, 2018 01:29 AM
Report

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

CustomeValue

0001-180001 0001-180001,0001-180002 0001-180001,0001-180002,0001-180003 0001-180009 0001-180006,0001-180008,0001-1800015,0001-1800020

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

Company

Explore

Follow us

Contact Us