SQL in comma separated enum list

[afauquem]

Hi,

I'm using an Enum dynamic attribute so it stores value in comma separated list like 'AV,EP,DR'

I need to check if a value is inside this list like 'AV' is in 'AV,EP,DR' ?

I try some sql code in data analyzer but it doesn't work, some functions are not authorized like STRING_SPLIT or STRING_AGG

So, how can i do that ?

SQL Query : 

SELECT a.customproperty12 as VALUE,(SELECT attribute1 AS VALUE FROM dataset_values WHERE datasetname="XXX" and attribute2 in (a.customproperty12)) AS LABEL FROM accounts AS a JOIN user_accounts AS ua ON a.ACCOUNTKEY = ua.ACCOUNTKEY JOIN users AS u ON ua.userkey = u.userkey WHERE a.ENDPOINTKEY=XX

Result :

Thanks

[Raghu]

you can use it like commnd right

SELECT a.customproperty12 as VALUE,(SELECT attribute1 AS VALUE FROM dataset_values WHERE datasetname="XXX" and attribute2 in (a.customproperty12)) AS LABEL FROM accounts AS a JOIN user_accounts AS ua ON a.ACCOUNTKEY = ua.ACCOUNTKEY JOIN users AS u ON ua.userkey = u.userkey WHERE a.ENDPOINTKEY=XX and a.customproperty12 in like '%AV%'

[afauquem]

I try this using '%' :

SELECT a.customproperty12 as VALUE,STUFF((SELECT ';'+attribute1 AS VALUE FROM dataset_values WHERE datasetname="XXX" and a.customproperty12 like concat('%',attribute2,'%')),1,1,'') AS LABEL FROM accounts AS a JOIN user_accounts AS ua ON a.ACCOUNTKEY = ua.ACCOUNTKEY JOIN users AS u ON ua.userkey = u.userkey WHERE a.ENDPOINTKEY=27 and u.userkey=1000

However the STUFF function is not allowed, as the STRING_AGG function

Any Idea ? I tried these functions because without I have this error "Subquery returns more than one row"

Thanks

[afauquem · Accepted solution]

I found a solution : 

SELECT a.customproperty12 as VALUE,(SELECT GROUP_CONCAT(attribute1) AS VALUE FROM dataset_values WHERE datasetname="XXX" and a.customproperty12 like concat('%',attribute2,'%')) AS LABEL FROM accounts AS a JOIN user_accounts AS ua ON a.ACCOUNTKEY = ua.ACCOUNTKEY JOIN users AS u ON ua.userkey = u.userkey WHERE a.ENDPOINTKEY=27

Thanks !