27.1.13

SQL Server 2005/2008: SPLIT-function for strings

When processing data in a database table, sometimes you need to split a string value in a column, which has format "Val1Val2...ValN", into parts Val1, Val2, etc. Example:
given:
ItemID  |  ItemProperty
4511     | Black / Yellow / Red

desired:
ItemID  |  ItemPropertySingleValue
4511     |  Black
4511     |  Yellow
4511     |  Red

Since T-SQL doesn't have such a function by default, I searched a bit in the Internet and found a nice idea to use XML data type to do the job. Here is a T-SQL function, which takes as parameters key column value, string to split and separator and returns a table, containing of pairs key value + substring. Additionally an index/position of each substring in the original string is returned - in this way values form certain levels/depth can be selected.

CREATE FUNCTION [dbo].[splitCategoriesForItem]
(
       -- Add the parameters for the function here
       @pItemNr nvarchar(255), -- ID of the item
       @pCategoriesString nvarchar(255), -- string of categories to split (in the case of pixi-export - column TonTrText
       @pSeparator nvarchar(1)
)
RETURNS
@tblResult TABLE
(
       PrdKey nvarchar(255), -- ID of the item
       Value nvarchar(max), -- extracted value
       valueIndex int IDENTITY(1,1) -- index/position of the value in a string
)
AS
BEGIN

             WITH Vals AS (
                    SELECT
                           @pItemNr as ItemKey
                           ,CAST('''' + REPLACE(@pCategoriesString, @pSeparator, '''') + '''' AS XML) XmlColumn
             ) -- close CTE

             INSERT INTO @tblResult
             SELECT
                      _res.PrdKey
                    , _res.Value
             FROM (
                    SELECT 
                             ItemKey AS PrdKey
                           , LTRIM(RTRIM(C.value(''.'',''varchar(max)''))) AS Value
                    FROM    Vals
                    CROSS APPLY Vals.XmlColumn.nodes(''/d'') AS T(C)
             ) _res
      
       RETURN
END

Feel free to contact me in the case of questions.
 

No comments: