splitting and storing in string to use in sql command

Question

i have id and program_name
like 1 abc
2 xyz
3 pqr
on selection of programs i am storing data in database as ~1~3(if 1 and 3 is selected)
what i want to do now is that i want to write a query where program id not in(1,3)

since when i am retrieving data from database, i am getting data as ~1~3 and after splitting the data i am storing it in array but how can i use that with sql query with not in command

nishant ranjan · Answer

no there can be any number i.e it could be like this ~3~54~23~78~12~ or ~5~7~12 or ~1 and we have to use in runtime not in (354237812) or not in (5712) or as data we have in database

Vulpes · Answer

If there are always two single digit numbers in the id field then something like this might work WHERE (ASCII(SUBSTRING(id 2 1)) - 48) NOT IN (1 2 3) AND (ASCII(SUBSTRING(name 4 1) - 48) NOT IN (1 2 3)