17
Views
4
Comments
Correct Use of the SQL Functions 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""]" 

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 

However when I test my SQL I get no rows returned. Please assist.

Rank: #429

Hi,

Is your application is muli tenant? If yes you might be using the wrong tenant

Hi! 

Could you kindly test an Advanced SQL just with the expression below?

SELECT     value  FROM     STRING_SPLIT('red,green,,blue', ','); 


This is just the example in the SQL Server tutorial but it seems the function do not work in OutSystems

Hope this help you

Graça

Hello Boipelo,


You don't need to use SQL function for Substring, there is an inbuilt function in OS, see below:

I would like to suggest you kindly go through this link for your better understanding of these 2 function :

https://www.outsystems.com/forums/discussion/50322/how-to-use-string-split/ 


Regards,

Sanjay