Solved

How do you check to see if query results contain a string?

Posted on 2008-10-02
1
251 Views
Last Modified: 2012-05-05
I have two queries; the first one simply returns a list (of, say, all letters of the alphabet) and the second  returns a varying number of letters. What I want to do is print out the results of the first query highlighting the ones that are contained in the second query. I'm guessing it would be done something like the following:

<cfloop query="qAlphabet">
   <cfif [qSelectLetters contains qAlphabet.letter]><b></cfif>
   #qAlphabet.letter#
   <cfif [qSelectLetters contains qAlphabet.letter]></b><cfif>
   <br/>
</cfloop>

But I'm unsure of how to phrase the part in the brackets. I've done it by setting a string (looping through query 2, to give something like "a,c,g,w" and seeing if that string contains qAlphabet.letter, but it seems like that would be excessively huge if the queries were involving blocks of text, not just letters.
0
Comment
Question by:SiobhanElara
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 15

Accepted Solution

by:
danrosenthal earned 250 total points
ID: 22629267
The syntax for your example would be something along the lines of:

<cfif LISTFIND(valuelist(qAlphabet.letter),qSelectLetters)>

But, you are correct in that it wouldn't be wise to use that solution for larger resultsets.

I would recommend doing the comparing at the SQL level. Either in the original query using a LEFT JOIN, or by using a query of queries.

Rough example of how you would use a LEFT JOIN to acheive the desired result:

SELECT
   a.value
   , CASE WHEN b.joinID IS NOT NULL THEN 1 ELSE 0 END as isrecordfound
FROM allrecordtable a
LEFT JOIN myvaluetable b
ON a.joinID = b.joinID

And your CFIF inside the output loop would be simply:

<cfif isrecordfound eq 1>


0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Cold Fusion Migration 4.5 --> 10 !! 4 54
Test if a nested structure variable exisits : Coldfusion 8 10 49
REGEX HELP 11 57
trying to resize an Image, using CFimage tag. 8 56
This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question