FUNCTION STRING_SPLIT()
 The STRING_SPLIT function applies from the version of SQL Server 2016 and later

A table-valued function that splits a string into rows of substrings, based on a specified separator character.
  
Return Types: 
Returns a single-column table whose rows are the substrings. The name of the column is value. Returns nvarchar if any of the input arguments are either nvarchar or nchar. Otherwise returns varchar. The length of the return type is the same as the length of the string argument.

Remarks
STRING_SPLIT inputs a string that has delimited substrings, and inputs one character to use as the delimiter or separator. STRING_SPLIT outputs a single-column table whose rows contain the substrings. The name of the output column is value.

The output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string. You can override the final sort order by using an ORDER BY clause on the SELECT statement (ORDER BY value).