Varchar value split in SQL

Question

I have a column in a table

Table 1

Tid.   name   fid
-----------------
 1.    Abc.   233
 2.    def.   344
 3.    xyz.   455

Table 2

did.   Status.   name                   FID
-------------------------------------------
 1      Open.        Abu,def,xyz            233,344,455

Now I want to split these fid and name from table2 in a stored procedure like this:

ID   status   name   FID
---------------
 1.   Open.     Abc.   233
 2.    Open.    Def.   344
 3    Open.     xyz.   455 

Update stored procedure

Query:

Create procedure as splitdata 
As 
Begin 
   Declare @fid varchar (500) 

   Select name, select item as fid 
   from spiltstring(@fid,',')) 
   from table1 
   Inner join table2 on table1.fid = table2.fid

This shows error

Conversion failed when converting the varchar value '233,344,455' to data type int

In table1 fid is int and in table2 fid is varchar type.

Split string function is

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

How can I do this?

UPDATE

Ok according to @john solution I try this solution ...

But as I have multiple other tables

Like

Create procedure as sp2
    @tick int
As
    Select
        Table 4.column, Table 5.column, Table 3.column,
        --so here I used solution like this
        (Select b.fid 
         from table2 t2 
         cross apply 
             (Select fid = s2.retval 
              from [udf-str-parase] (t2.fid, ',') s2) b),
        Table 6.column
    From 
        Table 2
    Inner join 
        table4 on table4.id = table5.id
    ..........
    Where 
        tick = @tick

When I try to execute only cross apply query this shows perfect result

But when I try to execute whole stored procedure like this

Exec sp2 28

I get this error:

Subquery returned more than 1 value.this is not permitted. When the subquery follows =,!=,<=,>=,>,< or when the subquery iIs used an expression.

Any solutions?

Answer 1

The the help of a split/parse function and a CROSS APPLY

Declare @Table2 table (did int,Status varchar(50),name varchar(50),FID varchar(50))
Insert Into @Table2 values
(1,'Open.','Abu,def,xyz','233,344,455')


Select ID = Seq
      ,A.Status
      ,B.Name
      ,B.FID
 From  @Table2 A
 Cross Apply (
         Select Name=S1.RetVal
               ,FID =S2.RetVal
               ,Seq =S1.RetSeq
          From [dbo].[udf-Str-Parse](A.Name,',') S1
          Join [dbo].[udf-Str-Parse](A.FID,',')  S2
            on S1.RetSeq=S2.RetSeq
       ) B

Returns

ID  Status  Name    FID
1   Open.   Abu     233
2   Open.   def     344
3   Open.   xyz     455

The Split/Parse UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>'+ replace((Select @String as [*] For XML Path('')),@Delimiter,'</x><x>')+'</x>' as xml).query('.')) as A 
    Cross Apply x.nodes('x') AS B(i)
);
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')