boipelo kedikilwe
Rank: #673
17Views
4Comments
Correct Use of the SQL Functions String_Split and Substring function
Question
JSON
SQL

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.

0
0
on 2020-10-21
Copy link to comment
Nelson Inácio
Rank: #429

Hi,

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

0
0
on 2020-10-21
Copy link to comment

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

0
0
on 2020-10-21
Copy link to comment

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

0
0
on 2020-10-21
Copy link to comment