Topic
  • 5 replies
  • Latest Post - ‏2010-06-25T19:36:00Z by newToDB2
newToDB2
newToDB2
33 Posts

Pinned topic Problem with Wildcard in SQL Statement

‏2010-06-24T20:32:08Z |
Hi,
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:

HOM_12345
HOM_34567_Q

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.

Jeff
  • newToDB2
    newToDB2
    33 Posts

    Re: Problem with Wildcard in SQL Statement

    ‏2010-06-25T12:08:56Z  
    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?

    Jeff
  • ocgstyles
    ocgstyles
    123 Posts

    Re: Problem with Wildcard in SQL Statement

    ‏2010-06-25T15:43:18Z  
    This doesn't work?

    
    select * from mytable where mycol not like 
    '%_Q';
    


    • Keith
  • newToDB2
    newToDB2
    33 Posts

    Re: Problem with Wildcard in SQL Statement

    ‏2010-06-25T16:37:38Z  
    • ocgstyles
    • ‏2010-06-25T15:43:18Z
    This doesn't work?

    <pre class="jive-pre"> select * from mytable where mycol not like '%_Q'; </pre>

    • Keith
    Hi Keith,
    No, it doesn't. And I would like to understand why. It must have something to do with the underscore character, which is also a single-character wildcard. But even at that, I don't understand why the filter doesn't work.

    Jeff
  • DavidSky
    DavidSky
    12 Posts

    Re: Problem with Wildcard in SQL Statement

    ‏2010-06-25T19:09:10Z  
    • newToDB2
    • ‏2010-06-25T16:37:38Z
    Hi Keith,
    No, it doesn't. And I would like to understand why. It must have something to do with the underscore character, which is also a single-character wildcard. But even at that, I don't understand why the filter doesn't work.

    Jeff
    You'll need to define an escape character in your statement, with something like ESCAPE '\' then use that character to escape the '_'

    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
  • newToDB2
    newToDB2
    33 Posts

    Re: Problem with Wildcard in SQL Statement

    ‏2010-06-25T19:36:00Z  
    • DavidSky
    • ‏2010-06-25T19:09:10Z
    You'll need to define an escape character in your statement, with something like ESCAPE '\' then use that character to escape the '_'

    So to expand on Keith's suggestion, the following should work:

    <pre class="jive-pre"> select * from mytable where mycol not like '%\_Q' ESCAPE '\'; </pre>

    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
    David,
    Thanks! Using the ESCAPE clause solves the problem.

    Thanks too for the link to the documentation regarding the use of LIKE. I didn't think to look there. My bad.

    I appreciate your help.

    Jeff