Understanding STRING_SPLIT in SQL Server

Leave a reply

What is STRING_SPLIT?

STRING_SPLIT is a built-in function in SQL Server that helps you:

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

Real-Life Example

Suppose you have a string: ‘Apple,Banana,Orange’

Then pass this string to the SQL Server STRING_SPLIT function then you get the following results:

Apple
Banana
Orange

Syntax

STRING_SPLIT ( string , separator )
Parameters:
string = Your input text
separator = Character used to split (only 1 character allowed)

Example of use in SQL Query:

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

Example with Table Data

Step 1: Create sample table

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(50)
);
INSERT INTO Products VALUES
(1, 'Laptop'),
(2, 'Tablet'),
(3, 'Phone'),
(4, 'Monitor');

Step 2: Use STRING_SPLIT for filtering

SELECT *
FROM Products
WHERE ProductName IN (
    SELECT value FROM STRING_SPLIT('Laptop,Phone', ',')
);
OUTPUT:
Laptop
Phone

Using JOIN (Better Practice)

SELECT p.*
FROM Products p
JOIN STRING_SPLIT('Laptop,Phone', ',') s
ON p.ProductName = s.value;

Remove Extra Spaces (Important)

SELECT TRIM(value) AS CleanValue
FROM STRING_SPLIT('Laptop, Phone, Tablet', ',');

SQL Server 2022 Feature (Ordinal Position)

Note: ordinal shows position in string

SELECT value, ordinal
FROM STRING_SPLIT('A,B,C', ',', 1);
OUTPUT:
A   1
B   2
C   3