Use the table-valued function using a select statement as follows:
select data
from dbo.[StringSplit](''x,c,v,b'','','')
where data = ''x'''
The query above returns one row and one column.
Use the table-valued function using a select statement as follows:
select data
from dbo.[StringSplit](''x,c,v,b'','','')
where data = ''x'''
The query above returns one row and one column.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringSplit]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN Print 'Dropping [StringSplit]' DROP FUNCTION [dbo].[StringSplit] END GO Print '*********************************************************** Name: StringSplit Purpose: splits a string using the xml parser. ***********************************************************' Print 'THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" WITH NO WARRANTIES. THIS SOFTWARE IS PROVIDED AS DEMO ONLY. YOU CAN MODIFY AND USE AT YOUR OWN RISK.' GO Print '***********************************************************' Print 'Creating [StringSplit]' GO Create FUNCTION [dbo].[StringSplit](@data NVARCHAR(MAX), @delimiter NVARCHAR(5)) RETURNS @t TABLE (data NVARCHAR(max)) AS BEGIN DECLARE @textXML XML; --replace the delimiter with xml markup. --this converts a string item1,item2,item3 into <elm>item1</elm><elm>item2</elm><elm>item3</elm> SELECT @textXML = CAST('<elm>' + REPLACE(@data, @delimiter, '</elm><elm>') + '</elm>' AS XML); --select the nodes from the xml into table fields INSERT INTO @t(data) SELECT T.col.value('.', 'nvarchar(max)') AS data FROM @textXML.nodes('/elm') T(col) RETURN END GO Print 'Usage: Use table function as follows: select data from dbo.[StringSplit](''x,c,v,b'','','') where data = ''x'''