StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I use the following table function for this purpose. The comments contain an example:</p> <pre><code>CREATE FUNCTION dbo.funcSplitToTable /* Split a string into parts base on a separation character to produce a table that has one column containing the results of the split. EXAMPLE: SELECT * FROM dbo.funcSplitToTable( '~', 'MAINT~12221~10001~10/25/2004~CANCELLED~1' ) SELECT * FROM dbo.funcSplitToTable( '~', '' ) SELECT * FROM dbo.funcSplitToTable( '~', NULL ) SELECT * FROM dbo.funcSplitToTable( NULL, 'MAINT~12221~10001~10/25/2004~CANCELLED~1' ) SELECT * FROM dbo.funcSplitToTable( '', 'MAINT~12221~10001~10/25/2004~CANCELLED~1' ) RETURN: Table with one column containing resulting strings. */ ( @strSearch AS varchar(255) -- String to search for. ,@strText AS varchar(MAX ) -- Text to search for string. ) RETURNS @tblResult TABLE ( rowid int NOT NULL identity(1,1), result varchar(MAX) NOT NULL, PRIMARY KEY (rowid) ) AS BEGIN DECLARE @iLastPos int , @iPos int , @lngSearch int , @lngText int , @lngSubstring int , @strResult varchar(MAX) ; IF @strText IS NULL RETURN ; SET @lngText = LEN(@strText + 'X') - 1 ; IF @strSearch IS NULL SET @strSearch = '' ; SET @lngSearch = LEN(@strSearch + 'X') - 1 ; IF @lngSearch <= 0 BEGIN INSERT INTO @tblResult SELECT @strText AS result ; RETURN ; END SET @strResult = NULL ; SET @iLastPos = 1 ; SET @iPos = CHARINDEX( @strSearch, @strText ) ; WHILE @iPos > 0 BEGIN SET @lngSubstring = @iPos - @iLastPos ; IF @lngSubstring > 0 INSERT INTO @tblResult SELECT SUBSTRING( @strText, @iLastPos, @lngSubstring ) AS result ; SET @iLastPos = @iPos + @lngSearch ; SET @iPos = CHARINDEX( @strSearch, @strText, @iLastPos ) ; END SET @lngSubstring = @lngSearch + @lngText - @iLastPos ; IF @lngSubstring > 0 INSERT INTO @tblResult SELECT SUBSTRING( @strText, @iLastPos, @lngSubstring ) AS result ; RETURN ; END </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload