0
CREATE DEFINER=`root`@`localhost` PROCEDURE `TestExampl2_SP`(in Array_Value varchar(255))
begin

  declare i int default 0;
  declare  loopcount int default 0;
  declare  arrayChar  varchar(50) ;
  declare isexist int(10) default 0;
  declare existString varchar(50);
  declare notexistString varchar(50) ;

 set loopcount=( select LENGTH(Array_Value) - LENGTH(REPLACE(Array_Value, ',', '')));

    while i<=loopcount do
   SET i = i + 1;
     set arrayChar  =(

    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(Array_Value, ',', i), ',', -1));

    set isexist=(select count(*) from emp
                      where ename=arrayChar);                 

      if(isexist >0 ) then


          select  CONCAT_WS(',',existString,arrayChar) into existString;
      else

          select CONCAT_WS(',',notexistString,arrayChar) into notexistString;

       end if;


    END WHILE;

  select notexistString;


END

This is my Procure when i execute this Procedure whith input call TestExampl2_SP('a,m,n,x,y,z') i am getting notexistString ='x,y,z' but insetd of this i want result row wise i.e i have to split by comma like this :

**value**
x
y
z

please suggest me how i will implement this .

Dev Research
  • 61
  • 2
  • 12

1 Answers1

0

You can follow below code to split string in sql.

 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

And check using 

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