STRING_SPLIT: Splitting strings into multiple rows using SQL Server using a delimiter or separator

In this video, we will be looking at how to use a delimiter or separator to split a string into multiple rows.
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: https://rebrand.ly/querying-microsoft-sql-server
98-364: Database Fundamentals (Microsoft SQL Server): https://rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): https://rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): https://rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): https://rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): https://rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpivot-ssas-tabular-dax
----
In this video, I'm showing you how to split a string into multiple rows using SQL Server using a delimiter or separator.

If you're working with a large string that you want to split into multiple rows, then this technique is perfect for you. By splitting the string into multiple rows, you can more easily handle and analyze the data. Plus, this technique is easy to apply using SQL Server, so you'll be able to get started quickly!

If you have a string such as "Jacksonville,Tampa,Orlando,Miami,Tallahassee", how can you separate it into 5 different rows? In this video, we'll have a look at STRING_SPLIT (which you can use from SQL server 2016 onwards).
It uses 2 arguments - the string to be split, and the one character delimiter or separator.
It returns one column which is called "value". If you are using an Azure database, then you can use a third argument to return a second column called "ordinal", which numbers the output.
Here is an example of how you would use it:
DECLARE @strText AS VARCHAR(300) = 'Jacksonville,Tampa,Orlando,Miami,Tallahassee'
SELECT @strText as MyText

SELECT value as TheResults FROM STRING_SPLIT(@strText, ',')

You can also use CROSS APPLY to use it in conjunction with another table or view:
SELECT ProductDescriptionID, trim(Value) as Sentence
FROM [Production].[ProductDescription]
CROSS APPLY
STRING_SPLIT(Description, '.')

In Azure SQL, you can add the third argument, such as:
STRING_SPLIT(Description, '.', 1)