May 07
Transact-SQL doesn’t have arrays or nifty string functions like the C# System.String.Split () or java’s String.split() returning arrays in one swoop. Then again, SQL knows the SELECT … IN statement like
SELECT * FROM table1 WHERE id in (1,2,3,526)
so why no looping through comma-delimited strings?
A “Table-valued User-defined Function” returning a table variable is a handy and versatile workaround letting you query and loop through the values with near array-like ease.
The code could look like this:
CREATE FUNCTION [dbo].[StringToTable] ( @inputString nvarchar(max), @separator char (1) ) RETURNS @ResultTable TABLE ( [String] nvarchar(max) ) AS BEGIN DECLARE @stringToInsert nvarchar (max) WHILE LEN(@inputString) > 0 BEGIN SET @StringToInsert = LEFT( @inputString, ISNULL(NULLIF(CHARINDEX(@separator, @inputString) - 1, -1), LEN(@inputString) ) ) SET @InputString = SUBSTRING(@InputString, ISNULL (NULLIF (CHARINDEX(@separator, @InputString), 0), LEN(@InputString)) + 1, LEN(@InputString)) INSERT INTO @ResultTable ( [String] ) VALUES ( @StringToInsert ) END RETURN END
Again, the Transact-SQL string functions seem clumsy and inadequate compared with high-level languages. Short of using CLR functions in the database table-valued functions can put custom string operations at your immediate disposal.
May 29th, 2008 at 8:28 pm
Man, thank you very much for this. I was looking so hard for something like this and I found your site.
Thanx a lot
Leo