Use Wildcard to filter query results

Question

I have a dropdown created with results from a ColdFusion query. I am trying to figure out how to remove assoc_last names that begin with Test*. Is it possible to put a WHERE assoc_last = "test*" and have that remove values containing "test" in that database, like "testby" and so on?

<cfquery name="GetActiveEmps" datasource="dsn">
        SELECT assoc_userid, assoc_last, assoc_first FROM tco_associates
        WHERE assoc_status = 'ACTIVE'
        and WHERE assoc_last LIKE 'Test%' 
        and len(assoc_last) > 0
        ORDER BY assoc_last
    </cfquery>

Proper notation for a Wildcard would be `WHERE assoc_last LIKE 'Test%'` - try this link for more about the LIKE operator, which is what you'll use since you're comparing strings. http://www.w3schools.com/sql/sql_like.asp — TRose, Apr 05 '16 at 19:07
What error did you get? You can only have one WHERE clause but you can have as many AND clauses as you'd like. If you copy/pasted his comment into your query it's probably saying incorrect syntax near WHERE. It should be an AND clause — Matt Busche, Apr 05 '16 at 19:14
If the field assoc_last refers to a surname, you have to take into account that your query may return some real people. Also, `and len(assoc_last) > 0` is not only unnecessary, but might slow down your query. — Dan Bracuk, Apr 05 '16 at 19:35
@David Brierton - Things like the WHERE clause are very fundamental sql concepts. From your recent threads, it sounds like you may not be very familiar with SQL. I would strongly recommend reviewing some tutorials and/or taking a sql course, as resources like S.O. will only get you so far... :) — Leigh, Apr 05 '16 at 19:57
We all have to start somewhere :) However, one of the reasons I mention it is that typically some of the calculations you are doing on your other threads are typically achieved with a JOIN, rather than CF code, which is a lot simpler IMO. I know I asked this on [one of your other threads](http://stackoverflow.com/questions/36265968/cf-sql-creating-a-table-with-different-results), but do not recall seeing an answer. Do you really need separate queries? Are the queries working with different databases or just tables? Are they on the same server? — Leigh, Apr 05 '16 at 20:08
yes i normally do so i dont flood the questions with conversations not pertaining to the question — David Brierton, Apr 06 '16 at 12:23

score 1 · Accepted Answer · edited Apr 05 '16 at 19:41

1

Use the LIKE operator. Also, don't forget to use cfqueryparam anywhere there is user input.

<cfquery name="GetActiveEmps" datasource="dsn">
   SELECT assoc_userid, assoc_last, assoc_first 
   FROM   tco_associates
   WHERE  assoc_status = 'ACTIVE' 
   AND    assoc_last LIKE 'Test%' 
   AND    len(assoc_last) > 0 
   ORDER BY assoc_last
</cfquery>

edited Apr 05 '16 at 19:41

Leigh

28,765
10
55
103

answered Apr 05 '16 at 19:15

Matt Busche

14,216
5
36
61

@David Brierton - As Dan mentioned above, `len(assoc_last) > 0` is unnecessary since values matching 'Test%' obviously have a length greater than zero. Also, the query above returns *only* values starting with "Test". Your question asked how to *remove* those values. Might want to revise your question to avoid confusing future readers. – Leigh Apr 05 '16 at 22:19
oh sorry i just added the `NOT LIKE` to it but still have the `len(assoc_last) > 0` because if it is empty i do not want it to show. Is there a better way of writing it than that line? – David Brierton Apr 06 '16 at 13:04

Use Wildcard to filter query results

1 Answers1