Skip to main content
rjd49
Participant
July 7, 2014
Answered

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

  • July 7, 2014
  • 1 reply
  • 319 views

Is it possible to extract a substring in the WHERE clause of either <CFQUERY> or in a Query of Queries?  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 QoQ, 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!


This topic is closed to new replies. Start a new post to keep the conversation going.
Correct answer Carl Von Stetten

Couldn't you do a LIKE in the where clause instead:

SELECT email_address

FROM tblMembers

WHERE email_address LIKE '%@comcast.net'


That should get you the addresses you are looking for.

-Carl V.

1 reply

Carl Von Stetten
Carl Von StettenCorrect answer
Legend
July 7, 2014

Couldn't you do a LIKE in the where clause instead:

SELECT email_address

FROM tblMembers

WHERE email_address LIKE '%@comcast.net'


That should get you the addresses you are looking for.

-Carl V.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices