[SOLVED] How to query a dynamic list of strings using SQL?

How to query a dynamic list of strings using SQL?

I have a table Customers with multiple columns one of which is state.

I want a dynamic query : When my stateInput is empty I want all customers, if not I want customers that have the state given in my stateInput.

stateInput is a dynamic value I receive from my fastapi request params

stateInput = request.query_params['stateInput '] like so.

stateInput could be '' or 'NY,CA' or 'NY' or 'NY,CA,BAL'

How do I dynamically write an sql query for this ?

Tried the following but these are giving errors/not working :

Query 1 :

select * 
from Customers 
where (:stateInput = '' or state in (:stateInput))

Query 2 :

stateInputList = request.query_params['stateInput '].split(',')

select * 
from Customers 
where (:stateInput = '' or state in (:stateInputList))

Query 3 :

stateInputTuple = tuple(request.query_params['stateInput'].split(','))

select * 
from Customers 
where (:stateInput = '' or state in (:stateInputTuple))

Query 4 (gives FIND_IN_SET is not a recognized built in function name) :

select * 
from Customers 
where FIND_IN_SET(state, :stateInput)

Solution

Something like this would work for your limited scenario:

select * 
from Tbl
where (:stateInput = '' or charindex(','+ ste +',', ','+ :stateInput +',')>0)

select * from Tbl
where (:stateInput='' or ste in 
  (select value 
   from string_split(:stateInput,',')))