SQL Server - fnsplit - Asked By divya rocks on 25-Apr-07 07:58 AM

in my database(sqlserver) table  one field has data like  1,2,3,4

i split this  by using 'fnsplit' function(select * from fnsplit('1,2,3,4',',')

in sqlserver every keywords..functions appear in blue color or pinck color..  why this fnsplit  function not appear in blue . or  pink

** is this not authorised funciton...

a UDF (user defined function) to split a string into a TVF (Table Valued Function) that returns a table of the split items.

To use this, you will need to execute the source code for the function. If you do not have the code, it should be easy to find.  Here is one version for SQL Server:

CREATE FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END
IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END
GO
--Test
select * from fnSplit('1,22,333,444,,5555,666', ',')
select * from fnSplit('1##22#333##444','##')  --note second item has embedded #
select * from fnSplit('1 22 333 444  5555 666', ' ')