Tokenize a String
We try to avoid SQL Server user-defined functions unless we can be certain that there is no other way to achieve what it is we’re asking of them. They aren’t very performant as they calculate on a row by row basis rather than the set based operations that our esteemed RDMS is renowned for.
Having said this I am going to post a table function I created recently for use as part of an SSIS import package that transforms an incoming single row CSV into roughly 100 records each time it runs.
The content team in our organisation faces the soul-destroying task of entering records as well as comma delimited strings on to an Excel spreadsheet which in turn is imported into 2 SQL tables.
There is no straight forward way to split the csv string into a SELECT list, but here is what I have done…
Having created the function, you can call it:
SELECT ID, Word FROM fn_tokenizeString(1,'Here, are,some ,test ,words, , , Frank,',',')
September 14th, 2007 at 11:20 am
Just thought I should also mention here, that if you wish to parameterise a function call in SSIS, then you would have to do this by populating a variable with the required SQL (evaluate as an expression or use script component in the control flow), and using the SQL Command from variable option in the OLE DB component. Alternatively turn the function into a stored procedure instead.