• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Searching for Wildcard Characters

    May 23, 2007 Ted Holt

    If you happen to see a bald-headed old geezer staring at a shelf at the local home-improvement warehouse (motto: you can do it; we can laugh), that’s me. I don’t know what I’m looking for, but I’m foolish enough to think I’ll recognize it when I see it. My methodology is something like searching a database using wildcard characters, which is what today’s tip is about.

    You probably know that database query interfaces typically allow for two types of wildcards: a wildcard that matches exactly one character; and a wildcard that matches zero or more characters. In SQL, my database query interface of choice, these characters are the underscore and percent sign, respectively. For example, to find all rows (records) of a table (file) that have a capital C anywhere in column (field) DataField, use this SQL command:

    SELECT * FROM qtemp.SomeTable
     WHERE datafield LIKE '%C%';   
    

    But suppose you want to search for rows that have a percent sign anywhere in DataField. You can’t do this:

    SELECT * FROM qtemp.SomeTable
     WHERE datafield LIKE '%%%';   
    

    Instead, add an ESCAPE character to the query. In the following example, the backslash is defined as the escape character.

    SELECT * FROM SomeTable
     WHERE DataField LIKE '%10%%' ESCAPE ''
    

    The escape character tells SQL that the character that follows it is to be interpreted literally. This query searches for all rows containing the string 10%. The first and last percent signs are wildcards. The second percent sign is interpreted literally.

    I realize that some shops don’t have SQL, so here’s an OPNQRYF example. The wildcard characters for OPNQRYF are underscore (one character) and asterisk (zero or more characters.) You can change them to whatever you like by specifying the wildcard characters in the optional, second parameter of the %WLDCRD function. The following query retrieves records that contain B followed by an underscore.

    OPNQRYF FILE((SOMETABLE)) +
       QRYSLT('DataField = %wldcrd("*B_*" "&*") ') 
    

    The second parameter of wildcard means that the ampersand is to be used as the wildcard for one character, while the asterisk serves as the wildcard for zero or more characters.

    I also realize that many shops use Query for iSeries, or whatever it’s called these days. To the best of my knowledge, there is no way to override the wildcard characters. That sounds like yet another reason to dump Query for a better query tool.

    SQL rulz!

    –Ted



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    FalconStor

    Begin Your Journey to the Cloud with Hybrid Cloud Date Protection and Disaster Recovery

    FalconStor StorSafe optimizes and modernizes your IBM i on-premises and in the IBM Power Virtual Server Cloud

    FalconStor powers secure and encrypted IBM i backups on-premise and now, working with IBM, powers migration to the IBM PowerVS cloud and on-going backup to IBM cloud object storage.

    Now you can use the IBM PowerVS Cloud as your secure offsite copy and take advantage of a hybrid cloud architecture or you can migrate workloads – test & development or even production apps – to the Power VS Cloud with secure cloud-native backup, powered by FalconStor and proven IBM partners.

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  Join us at the Annual 2008 conference, March 30 - April 3, in Nashville, Tennessee Help/Systems:  SEQUEL is the single solution for all your business intelligence needs
    LaserVault:  AES Encryption for secure AS/400 backups with no changes to your system

    IT Jungle Store Top Book Picks

    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    IBM: SOA Fits Skills Shortage to a ‘T’ ICS Finds the Sophisticated Side of PHP

    Leave a Reply Cancel reply

Volume 7, Number 20 -- May 23, 2007
THIS ISSUE SPONSORED BY:

WorksRight Software
ProData Computer Services
LaserVault

Table of Contents

  • Send a Spool File from AS/400 with a Specific Subject Line and Message Body
  • Searching for Wildcard Characters
  • Admin Alert: i5 IPL Pre-Planning and Post-Planning Checklists

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle