Thanks Peter, butI'm not clear what Db would apply. The User Function doesn't apply to any specific Db as it can be called from anywhere. It passes the parameter to the function and returns a row of columns with the results.
I've tried it with a smaller test UDF (below) and also found it works with othe SQL Query Editors but not with your Database Browser (nor VIP).
Any other thoughts?
Thanks
Joe
============================================
/* Function is to split a delimited filed into a number of table items.
Delimiter can be any value including space.
The process is implemented as a a function.
The benefits of this method are that it doesn't use tempdb (so it won't be affected by anything that may lock tempdb), it is slightly faster, and you can use the function just like any other table -- for example, select c.* from customers c where c.city in (select item from fnSplit('Dallas,New York,Chicago',',') )
To test, use of of these where the second item is the delimiter:
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', ' ')
*/
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
