How to query a dynamic list of strings using SQL?

Question

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)

Why didn't you add a tag for your DBMS when you updated? How do you expect anyone to answer if we don't know what you're using? — Barmar
– Barmar, Commented Oct 3, 2024 at 20:27
Why a dynamic query? Also, STRING_SPLIT() has been a thing since SQL Server 2016. — AlwaysLearning
– AlwaysLearning, Commented Oct 3, 2024 at 21:38

tinazmu · Accepted Answer · 2024-10-03 22:51:55Z

1

Something like this would work for your limited scenario:

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

Or

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

answered Oct 3, 2024 at 22:51

tinazmu

5,5442 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to query a dynamic list of strings using SQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related