Narasiman nar

Narasiman nar

  • NA
  • 64
  • 22.1k

replace in sql server

May 21 2018 8:45 PM
i writeen one table valued function to split string
 
create FUNCTION [dbo].[StringSplit](@input NVARCHAR(MAX), @delimiter CHAR(1)=',')
RETURNS @returnTable TABLE(item NVARCHAR(100)) AS
BEGIN
IF @input IS NULL RETURN;
DECLARE @currentStartIndex INT, @currentEndIndex INT,@length INT;
SET @length=LEN(@input);
SET @currentStartIndex=1;
SET @currentEndIndex=CHARINDEX(@delimiter,@input,@currentStartIndex);
WHILE (@currentEndIndex<>0)
BEGIN
INSERT INTO @returnTable VALUES (LTRIM(SUBSTRING(@input, @currentStartIndex, @currentEndIndex-@currentStartIndex)))
SET @currentStartIndex=@currentEndIndex+1;
SET @currentEndIndex=CHARINDEX(@delimiter,@input,@currentStartIndex);
END
IF (@currentStartIndex <= @length)
INSERT INTO @returnTable
VALUES (LTRIM(SUBSTRING(@input, @currentStartIndex, @length-@currentStartIndex+1)));
RETURN;
END;
 
 
i am using above function in below select query
 
DECLARE @testString VARCHAR(100)
SET @testString = replace(replace('{"Product":"algoda","Product2":"Nao","Product3":"Nao1"}','{',''),'}','')
SELECT *
FROM [dbo].[StringSplit](@testString, DEFAULT)
     When i execute output as follows
             item
"Product1":"algoda"
"Product2":"Nao"
"Product3":"Nao1"
 
 
 from the above i want output as follows
 
      
             item
    Product1    algoda
   Product2    Nao
   Product3    Nao
 
 in the above function what changes i have to made to get above excepted output to remove the " and :.
 
 
 

Answers (1)