I'm trying to use a simple construct in a SQL statement to filter data, but it seems that the underscore character in the data is causing it not to work. I know there must be a way to escape the underscore, but I haven't been able to find out how.
The data in the column looks like this:
I need to be able to do this:
WHERE column not like '%Q'
Does anyone know a way to make it work? We're on version 9.5.
Thanks for your help.
This topic has been locked.
Pinned topic Problem with Wildcard in SQL Statement
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Re: Problem with Wildcard in SQL Statement2010-06-25T12:08:56ZThis is the accepted answer. This is the accepted answer.Many thanks to all who've put their brains in gear to come up with a solution.
In desperation I tried using the LENGTH function since the data is of uniform length except for those rows with the '_Q' at the end.
WHERE LENGTH(column) < 10
worked, so I've resolved the problem of being able to filter the data.
But what could be done if the data WEREN'T uniform in length. IS there a way to escape the underscore?
ocgstyles 060000N6VY123 Posts
Re: Problem with Wildcard in SQL Statement2010-06-25T15:43:18ZThis is the accepted answer. This is the accepted answer.This doesn't work?
select * from mytable where mycol not like '%_Q';
Re: Problem with Wildcard in SQL Statement2010-06-25T16:37:38ZThis is the accepted answer. This is the accepted answer.
DavidSky 1100008J8E12 Posts
Re: Problem with Wildcard in SQL Statement2010-06-25T19:09:10ZThis is the accepted answer. This is the accepted answer.
- newToDB2 270002Y23C
So to expand on Keith's suggestion, the following should work:
select * from mytable where mycol not like '%\_Q' ESCAPE '\';
The v9.5 documentation for this is at http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0000751.html
David Sky - DB2 Information Development
IBM Toronto Software Lab
Re: Problem with Wildcard in SQL Statement2010-06-25T19:36:00ZThis is the accepted answer. This is the accepted answer.