SQLServer – trasformare una stringa in tabella

Di seguito una funzione SQLServer utile per splittare una stringa separata da caratteri in una tabella.

Esigenza: dover ricercare tra gli utenti solo i record che hanno alcuni id e doverli restituire nello stesso ordine in cui gli id sono passati nella stringa

Esempio:  passata la string di ids: ‘6,50,1’ alla stored il risultato deve essere

6 John Smeet

50 Mark Black

1 Enry White

Soluzione:

1. creare una function sql che ci trasforma una stringa in una tabella

ALTER FUNCTION [dbo].[func_SplitStringToTable]
(
      @DataList NVARCHAR(MAX)
    , @Separator NVARCHAR(2)
)
RETURNS @tbl TABLE (
      RowIndex INT PRIMARY KEY
    , FromPos INT
    , ToPos INT
    , Items NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @LenSep INT
    SET @LenSep = DATALENGTH(@Separator) / 2
    
    IF @LenSep > 0 AND DATALENGTH(@DataList) > 0 BEGIN
        ; WITH res (RowIndex, FromPos, ToPos) AS (
            SELECT CAST(1 AS INT) AS RowIndex
                , CAST(1 AS INT) AS FromPos
                , CAST(CHARINDEX(@Separator, @DataList + @Separator) AS INT) AS ToPos
            
            UNION ALL
            
            SELECT CAST(RowIndex + 1 AS INT) AS RowIndex
                , CAST(res.ToPos + @LenSep AS INT) AS FromPos
                , CAST(CHARINDEX(@Separator, @DataList + @Separator, ToPos + @LenSep) AS INT) AS ToPos
            FROM res
            WHERE CHARINDEX(@Separator, @DataList + @Separator, ToPos + @LenSep) > 0  
        )
        INSERT INTO @tbl
        SELECT res.*, SUBSTRING(@DataList, FromPos, ToPos - FromPos) AS Items
        FROM res
        OPTION (MAXRECURSION 0)
    END
    RETURN
END

2. chiamando la function otteniamo una tabella dove nella colonna Items troveremo i nostri id:

SELECT * FROM func_SplitStringToTable('6,50,1', ',')

3. scrivere la stored che utilizza la function in inner join con la nostra tabella ed effettuare l’ordinamento

SELECT * FROM MyUserTable INNER JOIN  func_SplitStringToTable('6,50,1',',')myitems ON
myitems.Items=PP_User.user_id