QueryExecute(): How to use SQL wildcard % search with a LIKE clause?

Question

I am new to ColdFusion and am trying to query something and use it in the cfscript. I cannot use actual code here, but here is a general SQL query I hope will help. It's a old script and I am trying to change it from cfquery tags to cfscript tags.

<cfscript>
    sqlCF = queryExecute("SELECT primarykey FROM names, personnel  
                          WHERE name.primaykey = personnel.primarykey 
                          AND ( upper(personnel.ID LIKE upper(':id%') OR 
                                upper(personnel.userID) LIKE upper(':id%')
                              )
                        , {id={value = "xyz123", cfsqltype="cf_sql_varchar}}
                        , {datasource=person"}); 

writeDump(sqlCF);
</cfscript>

When I run it, it shows up as 0 queries, but when I hard code it in and change the :id% part of the query to xyz123% as the value it works. I just do not know how the LIKE('XXXX%'), with a wild card sign, should be passed or if this even looks right. Any ideas?

Nothing to do with the question, but if you're updating old code, may as well change to the newer [ANSI-99 JOIN syntax](https://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax) as well. — SOS, Jul 24 '18 at 18:54

Twillen · Answer 1 · 2018-07-24T18:51:08.140

6

You want to append the wildcard symbol to the string in your parameter, not inside your query string. Also notice when I used parameters, I'm not wrapping the parameters in quotes.

<cfscript>
    sqlCF = queryExecute("
        select primarykey
        from names, personnel
        where name.primaykey = personnel.primarykey
        and (
            upper(personnel.ID) LIKE upper(:id)
            or 
            upper(personnel.userID) LIKE upper(:id)
        )
    ",{
        id={
            value = "xyz123%",
            cfsqltype="cf_sql_varchar"
        }
    },
    {datasource="person"});
</cfscript>

edited Jul 24 '18 at 18:51

answered Jul 24 '18 at 18:40

Twillen

1,458
15
22

Darn, beat me to it! Also, the datasource name should be enclosed in quotes: `datasource="person"`. – SOS Jul 24 '18 at 18:45
1

True if its a string and not a variable. There were quite a few syntax issues with the posted example in regards to were quotes started and stoped, so I had to guess what should/shouldn't be quoted. – Twillen Jul 24 '18 at 18:49
Yep. I only know because I tested it with a different query, and CF complained about the lack of quotes. – SOS Jul 24 '18 at 18:55
I'm not a fan of it, but the applications I maintain are littered with `datasource=session.dsn`; so my mind opted for no quotes. I agree the quotes look better here. – Twillen Jul 24 '18 at 18:57
Oh, I see what you mean. That works because it's a variable. (It doesn't in the OP's case because the name is a string literal, so CF looks for a variable named "person" and errors when it doesn't find one). – SOS Jul 24 '18 at 19:33
Thanks guys!. You guys helped me out! I totally was not thinking the putting the wildcard in the parameter. I should of thought outside of my box and changed the query. – WeJava Jul 26 '18 at 15:58

QueryExecute(): How to use SQL wildcard % search with a LIKE clause?

1 Answers1