First query with my function
select *
from [dbo].fnSplitString('1|16|170','|')
Second query with data to split
select m.CategoryTree as s
from tblBuyOnlineMaster m
I want to join these two query to get all the categories from second query like a table column, like the first result
Help will be very useful.
Assuming your function fnSplitString is a table-valued function, you can use the cross apply operator to perform the correlated "join":
select *
from tblBuyOnlineMaster m
cross apply [dbo].fnSplitString(m.CategoryTree, '|') s
Note the cross apply operator is congruent to an inner join. If you want left join semantics, use the outer apply operator instead. See also https://technet.microsoft.com/en-us/library/ms175156%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396 .
Outer Apply will never have an impact for the given scenario.
fnSplitString is a black box and we have no idea what special case(s) will cause it to not return any rows. Adding the comment also makes this answer relevant to everyone and answers the "next logical question".fnSplitString will split a string based on a delimiter I dont think there will be any logic to filter records.