why cross apply return 4 rows although correct is return 3 rows ?

Question

i work on sql server 2017 when join with split string seprated | it is display 4 rows although it is must be 3 rows because it have 3 values only .

so how to make it display 3 rows only only

expected result

but my issue it display as 4 rows
so why not display as 3 rows

How to modify it to display 3 rows only

my sample data as below

create table #ChemicalConfilict  
   (  
   NULLflag  varchar(300),  
   )  
   insert into #ChemicalConfilict(NULLflag)  
   values  
   ('REACH Version|REACH Version|REACH|')  
     
   select p.NULLflag  
     FROM #ChemicalConfilict P  
     CROSS APPLY Z2DataCore.dbo.SplitString(P.NULLflag,'|')  

Answer 1

Hi @ahmed salah
Because there are three '|' in your string, which means the string will be splited into four values. If there are only two '|' like this: 'REACH Version|REACH Version|REACH'it will be divided into three values.
Try this:

SELECT VALUE AS NULLflag  
FROM #ChemicalConfilict P  
CROSS APPLY STRING_SPLIT(Left(P.NULLflag,len(P.NULLflag)-1),'|')  
  
SELECT p.NULLflag  
FROM #ChemicalConfilict P  
CROSS APPLY Z2DataCore.dbo.SplitString(Left(P.NULLflag,len(P.NULLflag)-1),'|')  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Z2DataCore.dbo.SplitString is a UDF (User Defined Function), not the built in STRING_SPLIT function. So we do not know what is doing or returning.

Your string has 4 values, the last value is either blank or NULL depending on how your UDF works.

You could do something like this also

    select p.NULLflag
      FROM #ChemicalConfilict P
      CROSS APPLY Z2DataCore.dbo.SplitString(P.NULLflag,'|') s
    WHERE s.[value] <> ''

Answer 3

It seems your UDF creates an empty row as well.

NULLflag value
REACH Version|REACH Version|REACH| REACH Version
REACH Version|REACH Version|REACH| REACH Version
REACH Version|REACH Version|REACH| REACH
REACH Version|REACH Version|REACH|