This handy little script parses a string and returns the results as a table. I know there are a ton of string parsing functions out there, but I thought I’d add to the list. 😉
The basic logic of it (using a CTE) is derived from a forum post I found years ago. The table-valued UDF, delimiter, etc. is all stuff that I added. So thus, while I can not claim complete credit, I still thought it’d be worthwhile to share. Plus this will be used in another script I will be posting soon. 🙂
/* Let's create our parsing function... */ CREATE FUNCTION dbo.dba_parseString_udf ( @stringToParse VARCHAR(8000) , @delimiter CHAR(1) ) RETURNS @parsedString TABLE (stringValue VARCHAR(128)) AS /********************************************************************************* Name: dba_parseString_udf Author: Michelle Ufford, http://sqlfool.com Purpose: This function parses string input using a variable delimiter. Notes: Two common delimiter values are space (' ') and comma (',') Date Initials Description ---------------------------------------------------------------------------- 2011-05-20 MFU Initial Release ********************************************************************************* Usage: SELECT * FROM dba_parseString_udf(<string>, <delimiter>); Test Cases: 1. multiple strings separated by space SELECT * FROM dbo.dba_parseString_udf(' aaa bbb ccc ', ' '); 2. multiple strings separated by comma SELECT * FROM dbo.dba_parseString_udf(',aaa,bbb,,,ccc,', ','); *********************************************************************************/ BEGIN /* Declare variables */ DECLARE @trimmedString VARCHAR(8000); /* We need to trim our string input in case the user entered extra spaces */ SET @trimmedString = LTRIM(RTRIM(@stringToParse)); /* Let's create a recursive CTE to break down our string for us */ WITH parseCTE (StartPos, EndPos) AS ( SELECT 1 AS StartPos , CHARINDEX(@delimiter, @trimmedString + @delimiter) AS EndPos UNION ALL SELECT EndPos + 1 AS StartPos , CharIndex(@delimiter, @trimmedString + @delimiter , EndPos + 1) AS EndPos FROM parseCTE WHERE CHARINDEX(@delimiter, @trimmedString + @delimiter, EndPos + 1) <> 0 ) /* Let's take the results and stick it in a table */ INSERT INTO @parsedString SELECT SUBSTRING(@trimmedString, StartPos, EndPos - StartPos) FROM parseCTE WHERE LEN(LTRIM(RTRIM(SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)))) > 0 OPTION (MaxRecursion 8000); RETURN; END |