1. What is STRING_SPLIT?

STRING_SPLIT is a built-in function in SQL Server used to:

👉 Break a single string into multiple rows
👉 Based on a separator (like ,, |, -, space, etc.)

It returns a table of values (rows) where each part of the string becomes a row.

Simple:

STRING_SPLIT is a table-valued function that converts a delimited string into multiple rows based on a single-character separator.

2. Real-Life Scenario

You receive data like:

'SQL,Oracle,MySQL'

But we need output in rows like

SQL
Oracle
MySQL

👉 STRING_SPLIT converts this automatically.

3. Syntax

STRING_SPLIT ( string , separator )
Parameters:
string → Input text
separator → Character used to split (only 1 character allowed)

4. Basic Example

SELECT value
FROM STRING_SPLIT('Apple,Banana,Orange', ',');

Apple
Banana
Orange

5. Handling Empty Values

Notice empty row (because of ,,)

SELECT value
FROM STRING_SPLIT('red,green,,blue', ',');

red
green

blue

Using STRING_SPLIT with Table (Real Use Case)

CREATE TABLE Employees (
    EmpID INT,
    Skills VARCHAR(100)
);

INSERT INTO Employees VALUES
(1, 'SQL,Azure,PowerBI'),
(2, 'Oracle,Linux'),
(3, 'SQL,Python');


Split Skills Column


SELECT EmpID, value AS Skill
FROM Employees
CROSS APPLY STRING_SPLIT(Skills, ',');

1  SQL
1  Azure
1  PowerBI
2  Oracle
2  Linux
3  SQL
3  Python

SQL Server 2022 Feature (Order / Position)

ordinal = position of value in original string

SELECT value, ordinal
FROM STRING_SPLIT('A,B,C', ',', 1);

A   1
B   2
C   3