, , ,

When taking existing data to put into a data warehouse one hopes that it is nicely formatted. This is seldom the case and recently I was faced with the problem that single field contained a rather dirty list. First I cleaned the list using REPLACE to replace the variety of delimiters with comma. Then I used this excellent T-SQL function to extract the items from the list.

 , @delimiter
 , @index_of_list_item_to_return

Here is an example of it in use,

      , FN_STRING_HELPER_SPLIT(list,',',1)

Things to note,

  • This is a scalar function, it will only return a single item from the list
  • You have to choose how many columns you extract
  • If you ask for a non-existent item it seems to return the last non-null item rather than null.
  • This does not create additional records, there is no transposition or un-pivoting of the extracted fields. This can be done manually by creating a view that that unions two queries that select item 1 and item 2 into a single column called item.
  • Extraction and un-pivoting can be done in a single operation using the APPLY function and table-valued functions but this code  is conceptually complex.