fn_split_xt (Transact_SQL)

See also: fn_split

This table-valued UDF add the functionality of VB/C# SPLIT funtion/methods to TSQL and returns a table of values from a delimited string. An enhaced version of fn_split UDF, adds text qualifier, multicharacter delimiter and the posibility to get the nth item from the list.  


fn_split_xt(@list, @delimiter,@qualifier,@item_no)



@list = NVARCHAR(MAX) , string containig the list of values to be split delimited by @delimiter parameter;

@delimiter = NVARCHAR(255), delimiter string used in the list for sepparating the values to split, allows multiple characters.

@qualifier = text qualifier surounding each value in the lise, removed from result set, allows multichar qualifier.

@item_no = item number to be retrieved from the list of values, 0 indicates to return all values.


Return type

Table with the following structure:

id BIGINT IDENTITY(1,1),item_no BIGINT,item_value NVARCHAR(4000)



id = record number in the result table

item_no = number of item retrieved from the list of values, when all items retrieved it is the same as id

item_value = individual value from the list split by the function









SELECT * FROM [Lib].[tsql].[fn_split_xt]('<"Boston"/><"New York"/><"Chicago"/><"Los Angeles"/><"Sacramento"/><"Buffalo"/><"Orlando"/><"Torornto"/><"Montreal"/>', '/><','"',6)  


1 6 Buffalo





Last edited Aug 10, 2012 at 11:59 AM by adudau, version 4


No comments yet.