How Can I Extract a Substring in WHERE Clause of <CFQUERY>?

Question

Is it possible to extract a substring in the WHERE clause in ColdFusion of either or in a Query of Queries? As an example, I am trying to use the following query to find all email addresses with the domain "comcast.net" (i.e. everything after the "@" in the email address). I am querying an MS-Access database table.

<cfquery name="test" datasource="membership">
      SELECT email_address
      FROM tblMembers
      WHERE MID(email_address, INSTR(email_address, '@') + 1) = 'comcast.net'
</cfquery>

If I attempt this as a query, I get the error message "[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression."

If I attempt it as a Query of Queries, I get the error message 'Encountered "MID ( email1 ,. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,'.

I would be very grateful for any help on finding something that works! Thanks for your help!

Access doesnt support like i dont think, but it does support wildcards. Why not something like where email_address = %comcast.net or if it does support like, where email_address like '%comcast.net' — crthompson

Matt Busche Matt Busche · Accepted Answer · 2014-07-08T00:04:30

You can use the right() function to accomplish this. If you didn't strip trailing whitespace when you put it into the database you can use the trim function as well

<cfquery name="test" datasource="membership">
  SELECT email_address
  FROM tblMembers
  WHERE right(email_address, 11) = 'comcast.net'
</cfquery>

You could also use the like operator, but it's very likely the above option is faster.

<cfquery name="test" datasource="membership">
  SELECT email_address
  FROM tblMembers
  WHERE email_address LIKE '*comcast.net'
</cfquery>

Also make sure you're using cfqueryparam for all your values if they're something the user is supplying.

How Can I Extract a Substring in WHERE Clause of <CFQUERY>?

1 Answers