Topic
  • 4 replies
  • Latest Post - ‏2013-04-04T21:09:24Z by krmilligan
PWConner
PWConner
44 Posts

Pinned topic Select where field is all one character

‏2013-04-04T15:23:49Z |
Does db2 4 i support regular expressions?

I want to compare a field to all zeroes. In sqlrpg-ese: select * from table where field = *all'0'

In regular expression terms: select * from table where field = "^0+" (but that didn't work)
Updated on 2013-04-04T21:09:24Z at 2013-04-04T21:09:24Z by krmilligan
  • B.Hauser
    B.Hauser
    287 Posts

    Re: Select where field is all one character

    ‏2013-04-04T16:27:39Z  
    You may try something like this:

    
    Select * From Table Where Field = Repeat(
    '0', Length(Field))
    


    Birgitta
  • PWConner
    PWConner
    44 Posts

    Re: Select where field is all one character

    ‏2013-04-04T16:33:57Z  
    • B.Hauser
    • ‏2013-04-04T16:27:39Z
    You may try something like this:

    <pre class="jive-pre"> Select * From Table Where Field = Repeat( '0', Length(Field)) </pre>

    Birgitta
    Thanks. Any idea when we can expect regular expressions?
  • NickLawrence
    NickLawrence
    69 Posts

    Re: Select where field is all one character

    ‏2013-04-04T17:57:24Z  
    • PWConner
    • ‏2013-04-04T16:33:57Z
    Thanks. Any idea when we can expect regular expressions?
    One solution would be to write an external function in an ILE language (such as C or C++), and invoke the function (or table function) from SQL.

    The ILE C/C++ runtime library functions include support for regular expressions.
    http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Frtref%2Fsc41560731.htm

    For simple pattern matching, the SQL LIKE predicate is an easier way to go. http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzch2like.htm

    The SQL LOCATE built-in function might also be useful for simple string searches. http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frbafzscalocate.htm
  • krmilligan
    krmilligan
    450 Posts

    Re: Select where field is all one character

    ‏2013-04-04T21:09:24Z  
    • PWConner
    • ‏2013-04-04T16:33:57Z
    Thanks. Any idea when we can expect regular expressions?
    I don't believe that regular expressions are part of the SQL Standard, so that's a big reason why they aren't supported by DB2.