Search code examples
oracle-databasewildcardsql-like

Select rows matching a pattern using LIKE

I am using an oracle-database with data having a string-attribute consisting of any valid XML. This XML looks like the following:

<tag><t1>[someIntValue]</t1><moreTags>...</moreTags></tag>

Now I want to select rows that do not have a particular value for someIntValue. I tried it out by using attr NOT LIKE '%<t1>1600</t1>%' where 1600 is the value I do not want to be selected. But the former also gives me rows that do not even have this tag . Is there any wildcard that negates the pattern (something like attr LIKE '%<t1>![1600]</t1>%')

Thanks in advance :)

Solution 
  • select ...
from ....
where attr like '%<t1>%</t1>%' 
and attr not like '%<t1>1600</t1>%'

    Someone who's quicker than me with regular expressions might give you a better answer, but this one should work well enough.