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:
AppleBananaOrangeSyntax
STRING_SPLIT ( string , separator )Parameters:string = Your input textseparator = Character used to split (only 1 character allowed)Example of use in SQL Query:
SELECT valueFROM STRING_SPLIT('Apple,Banana,Orange', ',');Output:AppleBananaOrangeExample 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 ProductsWHERE ProductName IN ( SELECT value FROM STRING_SPLIT('Laptop,Phone', ','));OUTPUT:LaptopPhoneUsing JOIN (Better Practice)
SELECT p.*FROM Products pJOIN STRING_SPLIT('Laptop,Phone', ',') sON p.ProductName = s.value;Remove Extra Spaces (Important)
SELECT TRIM(value) AS CleanValueFROM STRING_SPLIT('Laptop, Phone, Tablet', ',');SQL Server 2022 Feature (Ordinal Position)
Note: ordinal shows position in string
SELECT value, ordinalFROM STRING_SPLIT('A,B,C', ',', 1);OUTPUT:A 1B 2C 3