SQL CSV to Table

ALTER Function [dbo].[Csv2Table_fn] (@CSVList nvarchar(1000))
 RETURNS @Table table (id nvarchar(50))
AS
BEGIN
 DECLARE @sep AS nvarchar(1); SET @sep = ','
 
 IF RIGHT(@CSVList, 1) <> @sep SET @CSVList = @CSVList + @sep

 DECLARE @Pos smallint; SET @Pos = 1
 DECLARE @OldPos smallint; SET @OldPos = 1
 
 WHILE @Pos < LEN(@CSVList)
  BEGIN
   SET @Pos = CHARINDEX(@sep, @CSVList, @OldPos)
   
   INSERT @Table
   SELECT LTRIM(RTRIM(SUBSTRING(@CSVList, @OldPos, @Pos - @OldPos)))
   
   SET @OldPos = @Pos + 1
  END

 RETURN
END
GO

-- ESEMPIO:
DECLARE @S AS NVARCHAR(100)
SET @S = 'ROSSO, VERDE,BLU,   BOSO'
SELECT * FROM CSVToTable_fn(@S)

VERSIONE CON SEPARATORE PARAMETRICO:

CREATE FUNCTION [dbo].[Csv2Table_fn]
(
   @CSVList nvarchar(MAX)
 , @sep nvarchar(1) = ','
)
 RETURNS @Table table (id nvarchar(50))
AS
BEGIN 
 IF RIGHT(@CSVList, 1) <> @sep SET @CSVList = @CSVList + @sep

 DECLARE @Pos smallint; SET @Pos = 1
 DECLARE @OldPos smallint; SET @OldPos = 1
 
 WHILE @Pos < LEN(@CSVList)
  BEGIN
   SET @Pos = CHARINDEX(@sep, @CSVList, @OldPos)
   
   INSERT @Table
   SELECT LTRIM(RTRIM(SUBSTRING(@CSVList, @OldPos, @Pos - @OldPos)))
   
   SET @OldPos = @Pos + 1
  END

 RETURN
END
GO

UTILIZZATO PER SPLITTARE UNA STRINGA:

DECLARE @split table (Id nvarchar(50), Posizione int)
 
INSERT @split (Id, Posizione)
SELECT Id, ROW_NUMBER() OVER (ORDER BY GETDATE()) - 1
FROM GVSYS..Csv2Table_fn('38023454.XZQ.QWE.00029466.0768.txt', '.')


SELECT *
FROM @split s



-- RISULTATO:

Id     Posizione
---------------------
38023454            0
XZQ                 1
QWE                 2
00029466            3
0768                4
txt                 5

Trovato qui.