STRING_SPLIT is one of the more useful T-SQL enhancements that comes with SQL Server 2016 and Azure SQL Database to split a string.
Simply it takes a string and a delimiter and splits this into rows.
SELECT * FROM STRING_SPLIT(‘sql tom ato’,’ ‘)
The results are as expected;
It is important that the database is in compatibility level 130 for this to be successful otherwise we’ll get the error Invalid object name ‘STRING_SPLIT’.
You can switch to this mode in SQL Server 2016 and Azure SQL Database with ALTER DATABASE TestAzureDB SET COMPATIBILITY_LEVEL = 130
All newly created Azure SQL databases from June 2016 will automatically have a compatibility level of 130 as advised in this post –
Improved Query Performance with Compatibility Level 130 in Azure SQL Database
To read more about STRING_SPLIT see the STRING_SPLIT (Transact-SQL) article here – https://msdn.microsoft.com/en-gb/library/mt684588.aspx
STRING_SPLIT SQL Server 2016 and Azure SQL Database