lenya
3/22/2018 - 9:21 AM

func_Split

func_Split

Splitting the string in sql server

Try this function

CREATE FUNCTION [dbo].[func_Split] 
    (   
    @DelimitedString    varchar(8000),
    @Delimiter              varchar(100) 
    )
RETURNS @tblArray TABLE
    (
    ElementID   int IDENTITY(1,1),  -- Array index
    Element     varchar(1000)               -- Array element contents
    )
AS
BEGIN

    -- Local Variable Declarations
    -- ---------------------------
    DECLARE @Index      smallint,
                    @Start      smallint,
                    @DelSize    smallint

    SET @DelSize = LEN(@Delimiter)

    -- Loop through source string and add elements to destination table array
    -- ----------------------------------------------------------------------
    WHILE LEN(@DelimitedString) > 0
    BEGIN

        SET @Index = CHARINDEX(@Delimiter, @DelimitedString)

        IF @Index = 0
            BEGIN

                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(@DelimitedString)))

                BREAK
            END
        ELSE
            BEGIN

                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))

                SET @Start = @Index + @DelSize
                SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)

            END
    END

    RETURN
END

Example Usage – simply pass the function the comma delimited string as well as your required delimiter.

DECLARE @SQLStr varchar(100)
SELECT @SQLStr = 'Mickey Mouse, Goofy, Donald Duck, Pluto, Minnie Mouse'

SELECT
    *
FROM
    dbo.func_split(@SQLStr, ',')

Result will be like this

![Result][imgResult]

[imgResult]:data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMsAAACcCAYAAADRcF98AAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAABbmSURBVHhe7V1bbxVHEp5ftDIyQbZ/xybkZUGL0fkHKwHiaUkewkX4ZYmMiJSHVYT2kjXsCoGQUF4hjlAAO1EsNhdy2wQU7hgb1NvVZ3rc09MzXTNTPT1nTh2psWl3V1d/Vd9U95yp6eThw4eCC2PAPuD3gYRB8oPEGDFG4APJeyf/Iri4MWCSMElMH0gEf5wIwAWEycJkYbIgLhAcbZuvOIZ6keHIUkIcIMurV6+41MRgyBG5kiyj5VVRVWZHq8JVEBfu3jcBoz9+/LhQPvnkEwHl4sWLhfLhh3919nHJGWIdXFymmiySLyIry0KSp7oAeYbwKSPLMoDh+Pzyyy8Sm2Umy4D3eo0iS1lEUfWzHrJsnBbzSSISq4wuEVEM5M+fFhstxQFZHj16VCgmWeDvq/eFKh/deChG8kri6jMtddvb29MbWeyr6KeffppzQViO6M/m5qaA9rOzo2o3dTkzkYOrgbWsljLLlhMaky+++EKsrq4KwASIAvVAlqFubjHzYrIYrm+SA6ph3Q4fIApcPcFh5ufn65NFXBKjZCT/JfgQkuW3334TdoE5LkuSwNzhd9in6AJkcfXx1n32rngDIu3+vxn9PxPvviHr3nhXfObQwyszQp+pJgs4AVxB4eqpN7bmT+0w8BPaNiaLGQVyy7R5cTpdT22cns+WbvNQaUeOHElGYjSfLvXSJVmhv4eXEFkePHhQKGV7FoguWZSRZAJC3b171ymjIHf1HbF7925Z3hGrekyoAwKZdQ59XDrGqtva2preZRgQwPzYkQUcR6/HYd0O7VGRpbBn0VFlQ5yWkUkTZIcQRuRRZJLtK8ki9yy5vzv6I8hy//59YReY87179wRs6KFITqhlGBTzo5ekQBiXnFzdjWOSKMfEsX27xbEb6Zjn94nd+2SR9TccenhlRugz1WSxr6L2HgUiirnJRZMltwEHgiRCbfCdm38gz7gN3BRQUcXcm2gPtZdfObI4+iPI8uuvvwq7ACZAgEKEBdbAR94RE8sjFWUh0sAeDtq7ZGV118dkuQ4EOXZdtT0PxDmf1oMe0Ca7yMi/XR/rdv3Y7izi6r6uuvP78jdV9p1P55aTC5FM6lFzPD2PqSbLmTNn1CZWO4a5VtdLMHPdviwdBRVZ7LtVl0YiAbboqFHhyJdGYPS6kWVHYNYfQRZwcruULcMAG/MD/WZHI0UWTRiXPFV37ajYteuouKZ/3v1AvJ37/zVxdNcucfRaqo/ZLnlbfKBlqN9l30KdNY+sP8hNxNsf2HLrjLcj+/nz59O7DAOy2EsLvZmHiAKRRN06TZci0L7R3bBsg29EGR09NDESvTzTSzXrpgAQDkjo2uArEtr9q9kCe5YNGdHsosmiI6paikkMMrLIqCJviynho+XZ8R1C+X+XrKzu6hFJliPi6sZVcWTXXnHu3F6R7D0nNnQ9/CwsXXeJI1eh/Thi7DpyNR3DVSfnYcuA8bJx03k2Gm8HIyaL4VN62aUdRZHFWLM3J4tJEiCBXjJUbPDVqm1n05/IvY76fiVbfmk54/1Qkw3+V199JexikkUTRZHF+rISMIKoASSCC4hLVlYnnXRmRjqvHO/qkRn5+4zYe06OrevhZyJJ5NBHyzi3FzDLt9mpOyf2JjOSXOl8TLnpuEpOy/Gmniz60Q51yzQt5u1SqAOS6OKNLAR3h7sQAZHlyy+/LBRNCpMocMHIyAJRZTSrbgKoyJKSxSUrq7tyWBLksLgC48HvyVvirP5d1V8Rh2cS8dbZVB/dRv2cEYevQD20kb+fddRdOSve0jJB7tm3RFImt+54auxxefbs2XQvw2APAgRw/TTr4HddunDm0GMAWdbX1wulQBZJlNHqrFqS6g8QCW65w4VG3xVzycrqLh+SZDkkLtvj5eqXpcPriDsjDl0e63b50Ey2wZ85dBlVp56e0OPBGOYSL9MDP56ex1STJbRD9lk+kOXOnTuFou9y6S8nIXJAycgiowpEFnUnTP7UZHHJ6l3d+2+K5M33nfPG6Pr06dPpjSx9dubQugFZbt26VSh6w75///7x3a60aLKoR1+AQMaSFSKzS1b8ujPi97kbB78Tf/pPcc5YPZksob2yp/KBLJ9//jm6AFn0bWLXzzqyJrXtkydPOLL01J+DqgVkuXnzJpcaGDBZgrpkf4VzWnHztOKhYsdpxf3lK2vWMwQysvz9n/8WXBgD9oFyH8iRJSaRISedP2MEGIswntAWVyZLGLu0ktrWqK0GH3Dntrj2hizwLBV/xggwFmE8oS2uTJYwdmklta1RWw0+4M5tce0/WTaWxILrbTBLsn5hqfVbXBr7Builx3fo2OZtNaVG7SsWjUHstuNgyAJvD3F+TKc0G5TVh8LfHs8mS464G2JpIc3+bKDPxGHRYI4xupTiilSmN5EF3lYyHLLImbQg88RhgXS22M1KcUUqliPL9vYr8WJzq/Py6tVr8dPP/xPO8ddOibm5U+KOrZdZD78bCWMn1uQc1N8PisW58WPtcycvihPZ7+vjOZb2OyVOLO7krC9eWM/6JloXe/yCjhfFYnJQrNTEcyKxqDnHmD6G5IWzWY4sW5Isz1+87LyAg/zw40/COf6dkwYRtANLJ4T6uZPi9ot1cXxuThy/k+qt61W/tN7+3dsvEQdWHPJUP6se/p/pYmJn6YXEdSKxQM4thm/pMTWuE0+W169fi+9/+LGcLKaT2s7qJJMkyYomk+XMOTLZr5G1+plEsAlh/t9JlgviAESWmo40kVjUnGMM0mhc6ciytS2ePd/svLySZLn3vSSLa/zbY6e/Zeul6+Fnsij+VfZ3qDdl1Oln9rX1cMk0dSjT24PvRGIRwWfq+qnGdbrJ8nxNLsNg2ZSSXJOnzJmzekS/xmSxZNdwplZkiYVFjfnVdXKq9uRkeSmv7E+fvei8wHry2+++F87xb50Qexzfs+w5sCj2zJ0Qt5S+K3LJo5dUc+K9W7IO+um/l/3u6weys756jEXxca4+bWPpeOBfzXCcSCwi+ExdP9W4kkWWzZdb4snT550XmMg3394TscaPMeeyMRmLMP6ncaUji7wF+PjJs84LnBj19Tffybfxxxk/xpzLxmQswvifxpWMLC82X4pHj592XrYlWf779bfyu48448eYc9mYjEUY/9O4kpFlbW1NcGEMhuwDZGSBw2hiFXgDe6yx+zYuYxHGDwHXNp/cN/iYo9BCtoHHUkA+/xzjwIUWgzZEgb4FsrQV2LQ/OMbPDx5zkRi0fTq2qQ2G3I8C016RBSIKE+axiqz8oUVgcGRhoowjK4VhaV1t8qVRYMqRpYdLP44s9OSMQBbCI7gtPPB7ln+IPyR/FB/10MmpIqPXsDq9OJe7PM7OTDCp1i0S00rduK1O9PzISfRiihgfGVlSQ6jnn4jOq3eQpXrPclP8Gc6FVzoMmyzeyKKcfSH/DgLtrEAWHxl8f0c4TqGJT6cmMgn7dEgWrTVHFqroUSXHa9jU2ZdGC2IpPbxZyDM1F+TxF6iXeAQjy5JorBMhMVyivJgixi9ElurU4mapspg0Uv39it8Zh78Mw0UWGUGAIClbLgFxLqVvnMmRwTyjUx+hbr4ZJ/278001YzKqE56NJR/8X4+b+Zges0wnaJh7O026Qql4EcjG0kJ2qlk2Xk6GcbHwOHsQslSnFjfL/sNkxvGeZec7Jq9htYNljiYd3lx+ZfX2W2aAGAap1CnRpsNBe+P/5jjZ8rtkdeHTSb60ynzjjSKCOs7deqWVOSc9piIIkKtEP0RU8GKKkFGILDHJgvuehSPLjoOB80gngmPNTccrOK7lCebV2bxJ4Hwv2U50UU31WLZz5QhaoZPuV6ajRXTwCXdU0ftXXHQJQ5bK1GKILI4UXoJMOY4sDSKLWtnARn9BKEe2HbBsb5K7UhvvN8vqHZfZlCRquaf3SWYzY6xKndBk2RGuloEQWar0i7IMi0gWjixjwqD3LNk+wFr/o5dheh+h73Bay7acc1p7m9LIYsks0SVbhqmloHGHFUipl5TZElEvv6r0q2ZLkMhSnVoMqbVpWi1xKilHlmaRJecizqWNb4M/jk5J5phm+3wUcW7s7UjhJVG6fDK+DzI38olxS9y5wVfkqrcEA5WCkCVWai/+btjwH7b0RhbEZpS+SbivDeh1LUoMQ5ZIqb34yDJ8slAYltQBYWlkbrRJhXcjjALTwt2wWKm9HFl2LgL9jCzdOHWoUYKQJVZKKUeWGnuWUB41YLlByBIrxZYjC0eWkFwNQhYQGrNwSjGnFIfyv7ZkRD513HYYf38K5vtHmYwWjAW9nSgwZbLQ26W1RArDtlZiYAIoMGWy9NApKAzbw2lFVYkCUyZLVBO6B6cwbA+nFVUlCkzRZBk/zJaWNkfxlkCGmUxoHaJa0xgcg4V6+td8cz8mnbhkghrXAGbtC6RhHndxzW7noTf4a7uTeMvQ8zlIFzr0xbI+LMZEsdK79QOItScx2Y+xYKfrxRQhCB1ZTFnqEWznc9qIEVtEltA6NNeetme1Ya0EqLZDh0gxbqtTgP5ByOI/sXh8cu/iBdpTjetNJkx0C2CjRiIrscA4tyt9Vy0KIONQL6chMplP8I7ECJM+3GhG8TvV8y+3vjUzJV+K2yfmROI6ELXlIZx1JqOWZC3W6PFNV61BLbKYexeFSUn6Liatt5C/EuZNPjHwr+NfZfrVIksookCOPnYyQycKGKoWWbRlfWm6ZbnuVj1s9ivTh2N4OsGYWP+qGqpIlpJMyVvHxxGlcGowQUoxHLKJmcw0EMVLFvulDcRk0Tn2penDBI4bQwTGv3x64ciysijvvoTJvden0Xon47oD5JvdhP69PhZmym+LZZjCy5M+PFRMEfMqkMWVVvzxAeM7lnSDuOf4HdJTjX0OkvuOJdWB+o4cAq9OmviwGPu0+T2L+XojayNv7u3MDb75njBr/1eZPtwJAvSDoDD1DFsgS8y0YnqIJlMihWGbz3yY37tQYFokS8S04uYGHlZPCsM2QmQA6cNl86bAtFdpxY0MPMBOFIYdICytpkSBaYEsMdOKW6ExoM4Uhh0QHCRTocDU8WLwMCfF+tKVKSZDgmoPhDAW9EagwLRXpxXDhLgwBqF8oC0FGz1I2XZQV38K5ofQK4ZMxoIedQpMmSz0dmktkcKwrZUYmAAKTJksPXQKCsP2cFpRVaLAlMkS1YTuwSkM28NpRVWJAlM0WczHTUKkn2ImE1qHqNY0Bvdi4Th0iNwmVWe7uNIjKHXC5OzUNJYXU4Q8HFlyKathHofwTqYDHRB4ddLEi0XBmQIkwzUhS45ELXSaaLLkXIQ4rTWV7XWQDnTohAmIQbxYuJyJ2sFak0VOtKlOTftVYOvFFGGXmqcVy1TitVNiLjkoVuQzZJhTiLFtak2mxXFpCEyiN/Fi4XQmI+K70orTPkvGW3r00s1+ojt35B6gYS+xypZhhfpUp7LEM4W077AlmpQBL6YIq9fPlJSPxx9YeSkwJxDXaYOdjD4NinyNjgCrqyZeLJxkcR8llzsVWNouw80XOUqOt1OpAWiypDrpI8c1gGWy9enJWXv7NOXmFvBiihBdiyxj518Xx+ULK6gJU28yLdbDCFBiN/FiURVZkOnDuSWSK3L40pRtkCh0MslE/I46L6YIo+MyJa3UYUgxnju+JnSWI8XPupMJ8TomBF6dNPFiUbVnqU0W6+ptk6RyCWXAQaFTjiz63HsjGrZA34spQjaKLCr//sCFlBxraWTZ7JQs/JK9Ksc0I21JWnGp01ecFlz2thjUMszUo2SMwgsbU+Kayzai/WkQspSdVmymFlOnFD+VJx9jJmNuRIeaUgyU8GLh+07D/HtZ+rBBntxpwbD80Udrm331ssg4TTh3MfboVHYisX+Db5+mjAgBjiZeTBFiOa0YAVLXTSgM27XOfR+PAlNOK+6hlSkM28NpRVWJAlNOK45qQvfgFIbt4bSiqkSBKacVRzUhk6Ur+IOQxZf+G+rvFJPpCvjQ4zAW9AhTYMppxZzKPDWp3G0piHvquO0oiP4UzEcMMxFNGAt6M1FgymSht0triRSGba3EwARQYMpk6aFTUBi2h9OKqhIFpkyWqCbku2FdwR+FLKHesF5nMqF06MpwvnG8WJQ9WhIgacqn66T83YspYiL1IktqpBDPZaEnE1AHBF6dNPFigX2y2NZ2isnkxRRh2RpkGSfyjEb0JxWDnrjJhNUBgVcnTbxYFJy+JCORyZIh4MUUYVl8WvGFgyJZvCjunJwXcyfXSVOKIfUYNRnI0pOpfkPOZUFdOHyRxfl3eRrxQnooletp4mQ4h626/B7lXx7CIDMlL4gDkHcvMyXhENa5E+sR0op3ciKYLOYR3UAAefLXhrS0L7sxR6Ky4/QQl9gJbNIZWUyCxCKLSRAmiyRLVQKWL/KAs2MzICeQGN1FlsJpxePMyEQn/6Q/u00rHl8FbR1C3Gjog294r4Lol02ks3FFHCZLbVOj0orNHPsQ+ffYo7317DiyeCKLekuKsQfRb2ThZVhtgpgdUKcVQ9qvLkCWWGnFTBYrUlTc7XKn8ep3dKVEcqUft3Kn/nb2RmuE6pxWjACp6yYUhu1a576PR4EppxX30MoUhu3htKKqRIEppxVHNaF7cArD9nBaUVWiwJTTiqOakMnSFfxByBIqbdgnl2IyXQEfehzGgh5hCkw5rZjTijmtGMnNGg9SIiU2bEbB/IZD964bY0FvEgpMmSz0dmktkcKwrZUYmAAKTJksPXQKCsP2cFpRVaLAlMkS1YR8N6wr+DskS/4os+yRcMKZ+icTXgfC6bQS5cVCP6aSO/4sfdjU9TRyK20qOvdFD8T8vJgiZCAjS5gTik39/JMJrwMCr06aeLFQD0TKjFWTGNpx7eMi6mhcN+3Yp0edsQO39WKKGB9HlrogIga2m3gn04EODdQO0gWLxZJM8VZJX/CRTxYvjGTpOrLI8aLrgbCCF1OEDFxasTqheCefJEpasfVGk6HmsoDNvIbVFw4gSMqWS0AcfWKWlb9SOKE4/XtZveJfDm+DlKZT+fQoyDGedrajYvp/82npzMYYXTzO7sW0CVm2tl8VU4ZXZP793ElxO+YBrLlTcvkAVhVBsmgrl6jm/02yuE4oTp2vcHJxJk+ffJx6EDrZzNLDOgYvd3Kykyz28eT6XEmDrA1XGN2RRZFkp4RILa47mSEngHmxyDm1dKj0RR6FHHxMerG++ttkszJjnTd1sHroq7YrY9McPyUXZMS6o4pe3ZREuoro4MW0UWQppBUXD1oNkS1ZdzJMFhlZ1GoJNvryFVWX0qWTK8JgnTUXkRBvezHIWKkHdnzDYcfnh0odVBRE6BJlGeYgS/60YnjTy5w4fjvmacXWcdSIq8IkNfFeOMyIYTqTfeVuGlnsU4TLHNanR9kyrCrtWb+pRvWFCGItuRuSx4spwkHQacV8WjECTaImXsNi9xCNyaJur8k8fs+yx6eHvcE39illpxc7N/gYXWJEls2XW+LJ0+edF6+DEDniJIhhLOitRIEppxXT26W1RArDtlZiYAIoMOW04h46BYVhezitqCpRYMppxVFN6B6cwrA9nFZUlSgwdXyDvy18KcAh/k4xmajWIBycsSAEMxVFgSmnFXNa8VSkFVPQL0cWCoEsgxEYKgJMlqFaludFjgCThRxSFjhUBJgsQ7Usz4scASYLOaQscKgIMFmGalmeFzkCTBZySFngUBFgsgzVsjwvcgSYLOSQssChIsBkGapleV7kCDBZyCFlgUNFgMkyVMvyvMgRYLKQQ8oCh4oAk2WoluV5kSPAZCGHlAUOFQEmy1Aty/MiR+D/RIeg1vGkuPwAAAAASUVORK5CYII=