You must Sign In to post a response.
  • Category: SQL Server

    String split in sql server

    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 :.
  • #769625
    Hello Rao,

    You can use the below statement for split the string in sql. It is available in 2016.
    SELECT * FROM STRING_SPLIT('Nirav,Lalan',',')

    Hope this will help you.

    Regards,
    Nirav Lalan
    DNS Gold Member
    "Failure is the path of least persistence"

  • #769648
    For sql server 2016 before, Try to create user defined function like below and use it

    CREATE FUNCTION [dbo].[SplitString]
    (
    @Input NVARCHAR(MAX),
    @Character CHAR(1)
    )
    RETURNS @Output TABLE (
    Item NVARCHAR(1000)
    )
    AS
    BEGIN
    DECLARE @StartIndex INT, @EndIndex INT

    SET @StartIndex = 1
    IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
    BEGIN
    SET @Input = @Input + @Character
    END

    WHILE CHARINDEX(@Character, @Input) > 0
    BEGIN
    SET @EndIndex = CHARINDEX(@Character, @Input)

    INSERT INTO @Output(Item)
    SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)

    SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
    END

    RETURN
    END


    SELECT * FROM [SplitString]('John,Jeremy,Jack',',')

    Thanks!
    B.Ramana Reddy


  • Sign In to post your comments