Correct Use of the SQL String_Split and Substring function

Question

I have the SQL code below, where the @Province input parameter is in this form: "[""Central District","Central Region","Delaware","Drenthe","Eastern Cape","Free State","Gauteng","Hardap Region","Haryana","Karas Region","KwaZulu Natal","KwaZulu-Natal","Limpopo","Lorestan Province","Mpumalanga","North West","Northern Cape","Pennsylvania","Qarku i Tiranës","State of Rio de Janeiro","Toscana","Wes-Kaap","Western Cape""]" However when I test my SQL I get no rows returned. Please assist.

WITH ProvinceList as (
    SELECT CAST (value AS nvarchar) name FROM STRING_SPLIT(SUBSTRING(@Provinces, 3, LEN(@Provinces)-3), ',')
)

SELECT {Site}.*
FROM {Site}

WHERE ({Site}.[Province] IN -- Get all Province names in list sent
        (SELECT name 
        FROM ProvinceList))
            
ORDER BY {Site}.[Name] ASC

score 1 · Accepted Answer · answered Oct 21 '20 at 12:59

After the STRING_SPLIT your list will consist of names like "Eastern Cape" and "Free State" with the quotation mark. Most likely you do not have quotation marks in your table, so you need to remove those characters from the input.

SET @Provinces = REPLACE(@Provinces, '"', '')
WITH ProvinceList as (
    SELECT CAST (value AS nvarchar) name FROM STRING_SPLIT(SUBSTRING(@Provinces, 2, LEN(@Provinces)-2), ',')
)

Correct Use of the SQL String_Split and Substring function

1 Answers1