SQL INSTR() using CSV. Need exact match rather than part
- by Alastair Pitts
This is a follow up issue relating to the answer for http://stackoverflow.com/questions/2445029/sql-placeholder-in-where-in-issue-inserted-strings-fail
Quick background: We have a SQL query that uses a placeholder value to accept a string, which represents a unique tag/id. Usually, this is only a single tag, but we needed the ability to use a csv string for multiple tags, returning a combined result.
In the answer we received from the vendor, they suggested the use of the INSTR function, ala:
select *
from pitotal
where tag IN (SELECT tag from pipoint WHERE INSTR(?, tag) <> 0) and time between 'y' and 't'
This works perfectly well 99% of the time, the issue is when the tag is also a subset of 2 parts of the CSV string. Eg the placeholder value is: 'northdom,southdom,eastdom,westdom'
and possible tags include:
north or northdom
What happens, as north is a subset of northdom, is that the two tags are return instead of just northdom, which is actually what we want.
I'm not strong on SQL so I couldn't work out how to set it as exact, or split the csv string, so help would be appreciated.
Is there a way to split the csv string or make it look for an exact match?
