Replace Character With Character Space

Regex.Replace(StringName.Trim(), @",", @" and ", RegexOptions.IgnoreCase);

Split String Scalar Valued Function

CREATE FUNCTION 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

GO

Test ::

SELECT Item

FROM dbo.SplitString('Apple,Mango,Banana,Guava', ',')