« , »

Using LIKE in Query of Queries SQL

July 12th, 2007 by Josh ColdFusion, General Web Dev.15 Comments

The Query-of-Queries (QoQ) feature of ColdFusion is especially useful for manipulating data that ColdFusion holds in a Query object but isn't actually a result set from a SQL query. For instance, LDAP results, directory lists, and FTP directory lists are all returned as Query objects by <cfldap>, <cfdirectory>, and <cfftp>.

That said, don't forget that QoQ supports the LIKE conditional for pulling the records you want. But you can search for more than 'StartOfPhrase%' when using LIKE.

I have an app that calls an LDAP query and then uses QoQ to find all User IDs (cn's) that start with "W" or "G". Why didn't I just make this filter in the LDAP call? With effort, I could have, but I was already filtering on different requirements in LDAP, and it was easier at this point to fine-tune the results with a QoQ. Previously, my code looked something like this:

PLAIN TEXT
SQL:
  1. SELECT * FROM LdapResults WHERE cn LIKE 'W%' OR cn LIKE 'G%'

Whereas that worked, it is needlessly verbose; this became more clear when I also had to check for IDs starting with "A", and I realized how uncomely that approach was. It also doesn't enforce the fact that the legit IDs will always be 6 characters in length.

So start using LIKE's other accepted symbols! Brackets [] let you specify a range of acceptable characters, and underscore _ lets you define how many characters to look for, versus the wildcard % which just accepts any length of characters.

PLAIN TEXT
SQL:
  1. SELECT * FROM LdapResults WHERE cn LIKE '[WAG]_____'

That query is more accurate and actually shorter (note: syntax highlighting makes the underscores difficult to see).

Finally, what if the first character should always be a "W", "A", or "G", but the remaining 5 characters should always be digits? The QoQ LIKE doesn't support repetition quantifiers like the curly braces {} do in RegEx. So you will have to manually repeat [0-9] for each character:

PLAIN TEXT
SQL:
  1. SELECT * FROM LdapResults
  2. WHERE cn LIKE '[WAG][0-9][0-9][0-9][0-9][0-9]'

That does start getting more verbose again, but the SQL is now very precise regarding its filter requirements. A little tweaking like this, and QoQ can be extremely handy.

15 Responses to “Using LIKE in Query of Queries SQL”

  1. Ben Nadel Says:
    July 12th, 2007 at 2:58 pm

    That’s pretty cool stuff. I am a big fan of % and _ in queries, but I have never really gotten into the whole [ ] thing. Thanks.

  2. Frank Wheatley Says:
    July 12th, 2007 at 3:12 pm

    Regex on QofQ. This could be useful. Remember there is a cost to using ‘like’ in queries.

  3. duncan Says:
    July 13th, 2007 at 3:37 am

    nice tip, never used the [] before… ps I think your first query example should be OR not AND.

  4. Josh Says:
    July 13th, 2007 at 1:44 pm

    @Duncan: Good catch! Oh, the woes of blog-coding. :-)

    Thanks everyone for comments.

  5. Jess Says:
    October 3rd, 2007 at 3:37 am

    how to make a query with searching name with (‘) single quote character?

    e.g

    select * from item where itemname like ‘% ‘ %’
    the quote between the % makes error. can you help me.

  6. Josh Says:
    October 3rd, 2007 at 10:54 am

    Jess, you can use two single-quotes together to escape the single-quote character.

    For instance:

    select * from item where itemname like '%''%'

    Hope this helps.

  7. jess Says:
    October 3rd, 2007 at 10:02 pm

    that’s it.. yeah.. it helps me a lot for beginners like me.

    thanks josh and thanks for this site for fast reply.

    I will add this site in my bookmark so I can update myself always

    have a Good Day!

  8. UPENDRA NARAYAN UPADHYAY Says:
    November 19th, 2007 at 3:16 am

    I WANT A STORED PROCEDURE WHICH WILL FILTER RECORDS BASED ON THE VALUE OF THE PARAMETERS PASSED TO THE PROCEDURE.IT IS REQUIRED TO SEARCH A DATABASE DEPENDING ON THE SEARCH CRITERIA GIVEN FROM THE FRON END. THE DATABASE USED IS DB2 UDB 8.1

  9. Joshua Curtiss Says:
    November 19th, 2007 at 9:41 am

    Sorry, don’t know DB2.

  10. M Prasath Says:
    March 27th, 2008 at 10:50 pm

    I want to search %20 in the set can you please help me

  11. Mark L Says:
    April 8th, 2009 at 8:52 am

    Thanks Josh. This primer helped me solve my query returning no rows problem (using the wrong bloody LIKE characters!)

  12. Doug Boude Says:
    November 8th, 2010 at 11:17 am

    This was EXACTLY what I was needing! I use QofQ so rarely, but just happened to have a need for it today and was getting quite frustrated at not being able to use a LEN() function in the WHERE clause. Your tips saved the day. :) Thanks!

  13. Josh Says:
    November 8th, 2010 at 12:51 pm

    Awesome! Thanks!

  14. Dan Fredericks Says:
    November 22nd, 2010 at 1:48 pm

    Hey,
    I noticed this since I am trying to use len in a query of queries, and it does not work. I need to get all values of my column where the length is 5. I tried ‘[0-9]_____’ in my where clause like stmt, but it still seemed to bring back records that were greater than 5. Any help would be greatly appreciated.
    thanks
    dan

  15. siva Says:
    June 28th, 2011 at 12:32 am

    how to first four character and last character like query

Leave a Reply

  Theme Brought to you by Directory Journal and Elegant Directory.