How to replace sub string by stuff ?

I work on sql server 2012 I need to replace substring by stuff separated stick |

SELECT DISTINCT
[InputID],PART_ID,[Vendor ID],Manufacturer,[Digi-Key Part No.] ,[Mfr Part No.],[Description],Category,Family ,Obsolete ,[Non-Stock]
, [Part_Status],partNumber,CompanyName,DKFeatureName2,[Variant Number],IsUnit,
SUBSTRING((
SELECT isnull(StarFormat,'') + Value+ isnull(endFormat,'')
FROM #FinalTable
WHERE ([InputID] = Results.[InputID] and DkFeatureId = Results.DkFeatureId)
--FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)')
,1,5000) AS Value--,Results.accepted_value_value
FROM #FinalTable Results
where StatusId=3

and what is meaning of using type.value for xml path

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajkiran Swain6y ago
https://www.sqltutorial.org/sql-string-functions/sql-replace/
Farhan Ahmed6y ago
Visit below link

https://www.c-sharpcorner.com/blogs/differences-between-stuff-replace-and-substring-in-sql-server1
https://medium.com/@arjunuttamsharma561/stuff-replace-and-substring-in-sql-server-50c2caf6ec65