SELECT [Value] FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

SELECT value
FROM STRING_SPLIT('An example sentence.', ' ');

-- Assume you have the CTE
WITH cte AS ( SELECT STRING_AGG(Country, ', ') AS ConcatenatedCountries FROM Country_Medals
)
SELECT value AS Country
FROM cte
CROSS APPLY STRING_SPLIT(cte.ConcatenatedCountries, ', '); -- Use STRING_SPLIT with CROSS APPLY
-- Split into words
SELECT CASE WHEN category LIKE '%: %' THEN SPLIT_PART(category, ': ', 1) WHEN category LIKE '% - %' THEN SPLIT_PART(category, ' - ', 1) ELSE SPLIT_PART(category, ' | ', 1) END AS major_category, -- alias the result
sum(businesses) -- also select number of businesses
FROM naics
GROUP BY major_category;

CREATE FUNCTION [dbo].[Func_SplitString]
( @Values VARCHAR(MAX)
, @splitStr VARCHAR(50)
)
RETURNS @Result_Table TABLE ( [value] nvarchar(MAX) NULL )
BEGIN DECLARE @TempStr nvarchar(MAX) WHILE (CHARINDEX(@splitStr,@Values)>0) BEGIN SET @TempStr=SUBSTRING(@Values,1,CHARINDEX(@splitStr,@Values)-1) INSERT INTO @Result_Table (value) VALUES (ltrim(rtrim(isnull(@TempStr, '')))) SET @Values = REPLACE(@Values,@TempStr+@splitStr,'') END/*End While*/ IF(LEN(RTRIM(LTRIM(@Values)))>0 AND CHARINDEX(@splitStr,RTRIM(LTRIM(@Values)))=0) BEGIN SET @TempStr=@Values INSERT INTO @Result_Table (value) VALUES (ltrim(rtrim(isnull(@TempStr, '')))) End /*End IF*/ RETURN
END

SELECT LEN(@String) - LEN(REPLACE(@String, ',', '')) + 1