TSQL - Parse address function, unable to return result

Question

Function px_explode will be provided with two parameters:

• separator
• string

Final result will look like this:

SELECT * FROM dbo.px_explode('xxy', 'alfaxxybetaxxygama')

and will return

But... Query won't finish execution, so I assume that I ran into an infinite loop here, now assuming this, my question might be.

How can I avoid the infinite loop I ran into and what am I missing?

---

Code:

CREATE FUNCTION dbo.px_explode 

    (@separator VARCHAR(10), @string VARCHAR(2000))

 RETURNS @expl_tbl TABLE 

    (val VARCHAR(100))

AS

BEGIN

IF (CHARINDEX(@separator, @string) = 0) and (LTRIM(RTRIM(@string)) <> '')

    INSERT INTO @expl_tbl VALUES(LTRIM(RTRIM(@string)))

ELSE

BEGIN

    WHILE CHARINDEX(@separator, @string) > 0

    BEGIN

        IF (LTRIM(RTRIM(LEFT(@string, CHARINDEX(@separator, @string) - 1))) 
   <> '')

            INSERT INTO @expl_tbl VALUES(LTRIM(RTRIM(LEFT(@string, 
 CHARINDEX(@separator, @string) - 1))))


     END

    IF LTRIM(RTRIM(@string)) <> ''

        INSERT INTO @expl_tbl VALUES(LTRIM(RTRIM(@string)))

 END

RETURN 

END

Answer 1

My favourite is the XML splitter. This needs no function and is fully inlineable. If you can introduce a function to your database, the suggested links in Gareth's comment give you some very good ideas.

This is simple and quite straight forward:

DECLARE @YourString VARCHAR(100)='alfaxxybetaxxygama';
SELECT nd.value('text()[1]','nvarchar(max)')
FROM (SELECT CAST('<x>' + REPLACE((SELECT @YourString AS [*] FOR XML PATH('')),'xxy','</x><x>') + '</x>' AS XML)) AS A(Casted)
CROSS APPLY A.Casted.nodes('/x') AS B(nd);

This will first transform your string to an XML like this

<x>alfa</x>
<x>beta</x>
<x>gama</x>

... simply by replacing the delimiters xxy with XML tags. The rest is easy reading from XML .nodes()