T-sql Function help

CREATE FUNCTION FnSplit
(
@List nvarchar(MAX),@SplitOn nvarchar(5))
RETURNS @RtnValue table
(id int identity(1,1), Value nvarchar(100))
AS
BEGIN
While (CharIndex(@SplitOn,@List)>0
BEGIN
INSERT INTO @RtnValue (value) Values (ltrim(rtrim(Substring(@List,1,CharIndex(@SplitOn,@List)-1))));
SELECT @List=Substring(@List,CharIndex(@SplitOn,@List)+len(@SplitOn),len(@SplitOn))
END

INSERT INTO @RtnValue(Value) Values (rtrim(ltrim(@List)))
Return

END

Select allOpen in new window

It would really help if you could provide some more details behind 'not working'.  Experts here do not have access to your data source and can't run queries, so no idea what that means.

Hello,

Please find the new script for splitting functionality

ALTER FUNCTION [dbo].[FN_Split]
(
  @sInputList VARCHAR(8000),
  @sDelimiter CHAR(1) = ','
) RETURNS @List TABLE (row [int] identity(0,1) not null, item VARCHAR(8000))
/**
select * from FN_Split('hello,world,this,is,a,test',',')
****/
BEGIN

DECLARE @sItem VARCHAR(8000)

SET @sItem = ''

WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
BEGIN
    SET @sItem= RTRIM(LTRIM(SUBSTRING(@sInputList,1, CHARINDEX(@sDelimiter, @sInputList, 0) - 1)))
    SET @sInputList= RTRIM(LTRIM(SUBSTRING(@sInputList, CHARINDEX(@sDelimiter, @sInputList, 0)+LEN(@sDelimiter),LEN(@sInputList))))
    INSERT INTO @List SELECT @sItem
END

INSERT INTO @List SELECT CASE WHEN LEN(@sInputList) < 1 THEN NULL ELSE @sInputList END

RETURN


END 

Select allOpen in new window

There are many split function available

This one is cited often as being very efficient (e.g. here) and there many many others

CREATE FUNCTION [dbo].[SplitStringIntoTable]  (
    @string varchar(8000),
    @delimiter varchar(5)
    )
  /*
    usage samples
    
      select *
      from dbo.SplitStringIntoTable('2011,2012,1013,2014,2015', ',')
    
      SELECT * FROM (SELECT 'ab/c/def/ghijklm/no/prq/////st/u//' AS string) AS test_values 
      CROSS APPLY DBA.dbo.SplitStringIntoTable(test_values.string, '/')
  */
RETURNS TABLE WITH SCHEMABINDING 
AS
RETURN
--Inline-CTE-Driven "tally table" produces values from 0 to 9999  ... enough to cover varchar(8000)
WITH cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS num
    FROM cteDigits [1s] --0 thru 9
    CROSS JOIN cteDigits [10s] --thru 99
    CROSS JOIN cteDigits [100s] --thru 999
    CROSS JOIN cteDigits [1000s] --thru 9999
),
cteBase(num) AS ( --Limit the number of rows up front, for both a performance gain and prevention of accidental "overruns"
        SELECT 0 UNION ALL
        SELECT TOP (DATALENGTH(ISNULL(@string, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM cteTally
), 
cteStart(num1) AS ( --This returns num + 1 (starting position of each "value" just once for each delimiter)
        SELECT b.num + 1
        FROM cteBase b
        WHERE (SUBSTRING(@string, b.num, 1) = @delimiter OR b.num = 0)
)
--Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. 
SELECT ROW_NUMBER() OVER(ORDER BY s.num1) AS value_seq,
       SUBSTRING(@string, s.num1, ISNULL(NULLIF(CHARINDEX(@delimiter, @string, s.num1), 0) - s.num1, 8000)) AS value
FROM cteStart s;
GO

Select allOpen in new window

also see this article:  Delimited list as parameter: what are the options?

Hi Jim ,
Afetr run script below, the results is
showing
DECLARE @Test nvarchar(MAX)
SET @Test='2005,2006,2007'
SELECT  Value  FROM  dbo.FnSplit(@Test,',')

RESULTS:
2005
2

This is I used for the MS report builder , when i used 2015 and 2014 (Multiple parameters) , i will get only 2015 results only