Function to split a string which is delimited by comma or other delimiter.
We often come across a problem when the all values needed are supplied as a list in a single string, generally separated by comma, hyphen, blank space or other delimiter. What is required is to split the sting and extract each value from it for us to be of any use in SQL statements.
Eg.: ‘C104, C584, C875, C946’
Following is the function dbo.Split1, which will take two inputs – string to be split and the delimiter used in that string. It will return the all the components of the string in form of table.
You can directly use the above function in a simple SELECT statement as follows:
You can also pass a string variable to the function.
It is most useful as a part of WHERE IN clause, which can be part a complex SQL queries.