How do I call this function in a sproc?

Question

I got this function from someone on here:

    create FUNCTION [dbo].[fnSplitString] (@s varchar(512),@sep char(1)) 
RETURNS table 
AS 
RETURN ( 
    WITH Pieces(pn, start, stop) AS ( 
      SELECT 1, 1, CHARINDEX(@sep, @s) 
      UNION ALL 
      SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) 
      FROM Pieces 
      WHERE stop > 0 
    ) 
    SELECT pn, 
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s 
    FROM Pieces 
  ) 

Normally in where clauses of sprocs I call this type of function like this:

WHERE u.[Fleet] IN (SELECT [VALUE] FROM dbo.udf_GenerateVarcharTableFromStringList(@Fleets, ','))   

How do I go about calling the above function in a similar manner?

Thanks!

Answer 1

You would want to replace [value] with [s].

Answer 2

Since you said the result of:

SELECT * FROM dbo.fnSplitString('abc,def', ',')

is correct, then it's likely that everything should work. Check what happens when you have an empty token (two adjacent commas) and see if the results are as you expect.

Also, I noticed there was an effective limit on how long the input string could be. Check to make sure that nothing is getting cut off.

Answer 3

The function does not remove leading/trailing spaces, so if you have a string 'a, b, c' and a separator ',' you'll get a table with 'a', ' b', ' c'. Does this match in your u.[Fleet]?