SPLIT_String is not recognized function name in SQL 2022

In addition to Marcin's post: string_split is only available in compatibility level 130 and higher.

No tally table is needed in SQL 2022 if you are compatibility level 160, because you can simply use generate series.

SELECT substring (str, value, 1)
FROM   (VALUES ('This is a string')) AS S(str)
CROSS  APPLy generate_series(1, len(str))

My goal was to find generation from the last name. At some point I had a where clause returning generations. However, as some point I deleted working condition and replaced it with a new condition and now it's not working and I don't understand why:

SELECT v.value, v.value AS Generation, Prov_Last_Name
     FROM #FirstResult
     CROSS APPLY STRING_SPLIT(Prov_Last_Name, ' ') AS v
	 WHERE Prov_Group = 'N' AND Prov_Last_Name <> '' 
	 AND (v.value LIKE  '% First ' OR v.value LIKE '% I' OR V.VALUE LIKE '% |' OR v.value LIKE '% V' OR v.value LIKE '% I V')

It appears that there are a few issues with your current WHERE clause:

WHERE Prov_Group = 'N' 
  AND Prov_Last_Name <> '' 
  AND (v.value LIKE  '% First ' 
       OR v.value LIKE '% I' 
       OR v.value LIKE '% |' 
       OR v.value LIKE '% V' 
       OR v.value LIKE '% I V')

1. The patterns have spaces in the wrong places. For example, LIKE '% I' will only match if the word ends with I but preceded by a space inside the word — not as a standalone word. Also, '% I V' (with a space) will never match “IV”.
2. '% |' seems like a typo. That doesn't look like a valid generation pattern.
3. You're missing some common generation terms. Typically, you'd want to catch suffixes like JR, SR, II, III, IV, V, etc.

Try the following instead:

SELECT 
    v.value AS Generation,
    f.Prov_Last_Name
FROM 
    #FirstResult AS f
CROSS APPLY 
    STRING_SPLIT(f.Prov_Last_Name, ' ') AS v
WHERE 
    f.Prov_Group = 'N'
    AND f.Prov_Last_Name <> ''
    AND (
        v.value IN ('Jr', 'Sr', 'II', 'III', 'IV', 'V', 'VI')
        OR v.value LIKE 'Jr.%' 
        OR v.value LIKE 'Sr.%'
    );

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Hi Marcin,

This is almost perfect except for cases where last name has two or more parts before generation. Ca you please help with this last hurdle?

SELECT DISTINCT v.value, f.Prov_Last_Name, TRIM(SUBSTRING(f.Prov_Last_Name, NULLIF(CHARINDEX(' ', f.Prov_last_Name),0) + 1, LEN(f.Prov_Last_Name))) AS Generation
     FROM #FirstResult f
     CROSS APPLY STRING_SPLIT(f.Prov_Last_Name, ' ') AS v
	 WHERE f.Prov_Group = 'N' AND f.Prov_Last_Name <> '' AND f.Prov_Last_Name LIKE '% %' 
      AND -- (v.value =  'First' OR v.value = 'JR' OR v.value = 'I' OR v.value = 'II' OR v.value = 'III' OR  v.value = 'V' OR v.value = 'IV')
	         (f.Prov_Last_Name LIKE '% JR' OR f.Prov_Last_Name LIKE '% I' OR f.Prov_Last_Name LIKE '% II' OR f.Prov_Last_Name LIKE '% III'
			  OR f.Prov_Last_Name LIKE '% V' OR f.Prov_Last_Name LIKE '% I V' AND f.Prov_Last_Name LIKE '% I I I' OR f.Prov_Last_Name LIKE '% SR')
			  AND (
        v.value IN ('Jr', 'Sr', 'II', 'III', 'IV', 'V', 'VI')
        OR v.value LIKE 'Jr.%' 
        OR v.value LIKE 'Sr.%'
    )
	  
	  ORDER BY v.value;

The exception cases are like this:

Prov_Last_Name Generation MC GINNISS V GINNISS V

Also I'm not sure if our statement catches case where generation is 'I V' (with a space) or 'I I I' There are a few cases like that.

Ok, this is the working statement which covers all the cases:

;WITH cteGenerations AS ( SELECT DISTINCT v.value,  f.record_sequence, f.Prov_Last_Name, 
	TRIM(SUBSTRING(f.Prov_Last_Name, NULLIF(CHARINDEX(' ', f.Prov_last_Name),0) + 1, LEN(f.Prov_Last_Name))) AS Generation
     FROM #FirstResult f
     CROSS APPLY STRING_SPLIT(f.Prov_Last_Name, ' ') AS v
	 WHERE f.Prov_Group = 'N' AND f.Prov_Last_Name <> '' AND f.Prov_Last_Name LIKE '% %' 
      AND -- (v.value =  'First' OR v.value = 'JR' OR v.value = 'I' OR v.value = 'II' OR v.value = 'III' OR  v.value = 'V' OR v.value = 'IV')
	         (f.Prov_Last_Name LIKE '% JR' OR f.Prov_Last_Name LIKE '% I' OR f.Prov_Last_Name LIKE '% II' OR f.Prov_Last_Name LIKE '% III'
			  OR f.Prov_Last_Name LIKE '% V' OR f.Prov_Last_Name LIKE '% I V' AND f.Prov_Last_Name LIKE '% I I I' OR f.Prov_Last_Name LIKE '% SR')
			  AND (
        v.value IN ('Jr', 'Sr', 'II', 'III', 'IV', 'V', 'VI')
        OR v.value LIKE 'Jr.%' 
        OR v.value LIKE 'Sr.%'
    )
	  
	),
	cteGenerations2 AS (SELECT  f.value,  f.record_sequence, f.Prov_Last_Name, 
	
	           CASE WHEN Generation IN ('JR', 'SR', '|', 'II', 'III', 'I V', 'V', 'V I') THEN Generation ELSE TRIM(SUBSTRING(f.Generation, NULLIF(CHARINDEX(' ', f.Generation),0) + 1, LEN(f.Generation) )) end
			   AS Generation
			   FROM cteGenerations f)
    SELECT * FROM cteGenerations2