StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You may find the solution in <em><a href="http://www.codeproject.com/KB/database/SQL_UDF_to_Parse_a_String.aspx" rel="nofollow noreferrer">SQL User Defined Function to Parse a Delimited String</a></em> helpful (from <a href="http://en.wikipedia.org/wiki/The_Code_Project" rel="nofollow noreferrer">The Code Project</a>).</p> <p>This is the code part from this page:</p> <pre><code>CREATE FUNCTION [fn_ParseText2Table] (@p_SourceText VARCHAR(MAX) ,@p_Delimeter VARCHAR(100)=',' --default to comma delimited. ) RETURNS @retTable TABLE([Position] INT IDENTITY(1,1) ,[Int_Value] INT ,[Num_Value] NUMERIC(18,3) ,[Txt_Value] VARCHAR(MAX) ,[Date_value] DATETIME ) AS /* ******************************************************************************** Purpose: Parse values from a delimited string & return the result as an indexed table Copyright 1996, 1997, 2000, 2003 Clayton Groom (<A href="mailto:Clayton_Groom@hotmail.com">Clayton_Groom@hotmail.com</A>) Posted to the public domain Aug, 2004 2003-06-17 Rewritten as SQL 2000 function. Reworked to allow for delimiters > 1 character in length and to convert Text values to numbers 2016-04-05 Added logic for date values based on "new" ISDATE() function, Updated to use XML approach, which is more efficient. ******************************************************************************** */ BEGIN DECLARE @w_xml xml; SET @w_xml = N'<root><i>' + replace(@p_SourceText, @p_Delimeter,'</i><i>') + '</i></root>'; INSERT INTO @retTable ([Int_Value] , [Num_Value] , [Txt_Value] , [Date_value] ) SELECT CASE WHEN ISNUMERIC([i].value('.', 'VARCHAR(MAX)')) = 1 THEN CAST(CAST([i].value('.', 'VARCHAR(MAX)') AS NUMERIC) AS INT) END AS [Int_Value] , CASE WHEN ISNUMERIC([i].value('.', 'VARCHAR(MAX)')) = 1 THEN CAST([i].value('.', 'VARCHAR(MAX)') AS NUMERIC(18, 3)) END AS [Num_Value] , [i].value('.', 'VARCHAR(MAX)') AS [txt_Value] , CASE WHEN ISDATE([i].value('.', 'VARCHAR(MAX)')) = 1 THEN CAST([i].value('.', 'VARCHAR(MAX)') AS DATETIME) END AS [Num_Value] FROM @w_xml.nodes('//root/i') AS [Items]([i]); RETURN; END; GO </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