I am trying to extract all the variable from the following string;
string = 'factor1|factor2|factor3'
This is just a test string. In real string, I do not know the number of factors. What I want is following;

ID | Var1 | Var2 | Var3 | VarN
1 Factor1 Factor2 factor3 FactorN

What I have tried is as following but I have to provide everything manually in order to get all the N factors. I want something generic. I have googled a lot and I have searched a lot but couldn't find anything useful.

 select SUBSTRING('factor1|factor2|factor3'
            ,CHARINDEX('|','factor1|factor2|factor3') + 1


Assuming that this question pertains to Microsoft SQL Server:

Identifying the most appropriate solution requires knowing more about:

  • What is the source of this string

  • What is the goal of splitting it and returning each value as a separate column?

That being said, the way to accomplish this in T-SQL is to use Dynamic SQL to SELECT a series of literal values that are the result of the input string being split. For example:

DECLARE @String NVARCHAR(MAX) = N'factor1|factor2|factor3';


SELECT @SQL += N''''
             + factors.SplitVal
             + N''' AS [Var'
             + CONVERT(NVARCHAR(50), factors.SplitNum)
             + N'], '
FROM   SQL#.String_Split(@String, N'|', 1) factors;

SET @SQL = STUFF(@SQL, LEN(@SQL), 1, N';');



You can use whatever string splitter you prefer, though SQLCLR-based splitters are the best choice. The one I used is from the SQL# library of SQLCLR functions (which I wrote, but String_Split is in the Free version).

This can easily be wrapped in a stored procedure as they can dynamically create their result set structure at runtime. On the other hand, it cannot be used in a TVF (T-SQL or SQLCLR) as those are required to have static result set structures.

Also, this code just returns a single row. The sample result set in the Question has an ID field which implies multiple rows. It is not clear, at this point, how that would even be passed in. But, if there are multiple rows then this gets a little more complicated:

  • The code presented here can be adapted to return additional rows of static values, combined into a single result set via UNION ALL between them

  • The code presented here can be adapted to instead dynamically create a local temp table containing the appropriate number of fields, then for each row, create an INSERT statement instead of the current SELECT to populate the temp table, and finally do a SELECT * FROM #tmp;. All of this would be done within the Dynamic SQL, even the final SELECT, so that the temp table can be kept as a local temp table, thereby allowing multiple processes to run at the same time (which would not work if using a global temp table).

  • Depending on where the input data is coming from and how it is being used, it is possible that a Table-Valued Parameter (TVP) would be best.

  • If this data already exists, as shown, in a table, then SQLCLR is an option as a stored procedure can be created to split a string into any number of fields and return each row as it is read from the table (i.e. streaming the data back). This is more efficient than T-SQL which needs to place all rows either in memory (in the Dynamic SQL local variable) or on disk (in a temp table). There is a stored procedure in the SQL# library (noted above) called String_SplitResultIntoFields that handles this particular scenario quite nicely and efficiently. This proc, however, is only available in the Full version (i.e. not free), but the benefit is that it already exists.

|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.