If we have one series with comma separate(any Delimiter) value and we want split into in rows, I have create function for that:
CREATE OR ALTER FUNCTION [dbo].[TF_SplitSeriesIntoRow]
(
@text NVARCHAR(MAX),
@delimiter NVARCHAR(20) = ','
)
RETURNS TABLE
AS
RETURN
(
WITH
-- Virtual numbers table banao — koi physical table nahi chahiye
E1(N) AS
(
SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1)) t(N)
),
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 100 rows
E4(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10,000 rows
E6(N) AS (SELECT 1 FROM E2 a CROSS JOIN E4 b), -- 1,000,000 rows
-- TOP se sirf utne hi rows lenge jitne string ki length hai
Tally(N) AS
(
SELECT TOP (ISNULL(LEN(@text), 0) + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E6
),
-- Har token ki start position dhundho
TokenStarts(start_pos) AS
(
SELECT 1
WHERE @text IS NOT NULL AND LEN(@text) > 0
UNION ALL
SELECT N + LEN(@delimiter)
FROM Tally
WHERE @text IS NOT NULL
AND LEN(@delimiter) > 0
AND N <= LEN(@text) - LEN(@delimiter) + 1
AND SUBSTRING(@text, N, LEN(@delimiter)) = @delimiter
)
-- Start position se token extract karo
SELECT
SUBSTRING(
@text,
start_pos,
ISNULL(NULLIF(CHARINDEX(@delimiter, @text, start_pos), 0),
LEN(@text) + 1) - start_pos
) AS value
FROM TokenStarts
);
GO
-- USE KARO — CSV string ko rows mein convert karo:
SELECT s.value AS SkillName
FROM dbo.[TF_SplitSeriesIntoRow]('SQL Server,,Python,Power BI,Azure', ',') s;
/*
Result:
SQL Server
Python
Power BI
Azure
*/