Solved

SQL Query where left operator is contained in right operator?

Posted on 2004-08-30
14
196 Views
Last Modified: 2013-12-24
In my CF application, I need to return results where a variable string contains the value of a Column.
If it was the opposite, it would be easy

e.g. SELECT * from Table WHERE ColumnA LIKE '#string#'

But I want to know if the left operator (table column) is within the right operator (variable string).

e.g. SELECT * from Table WHERE '#string#' LIKE ColumnA [can't do that, of course]

#string# will vary only in the last character which can be any number.

So if #string# can be foo1, foo2, foo59, etc. my ColumnA will contain "foo" indicating that
whenever the string contains "foo%" it will return the record. Is there a way to put a wildcard character
in the table column or is there another way to accomplish this.

 
0
Comment
Question by:451ls
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 21

Expert Comment

by:pinaldave
ID: 11937871
Hi 451ls,
you can do something like this...

#string# contains (queryname.recordcount)
and can write more about that...
I will go home and write you complite logic. I think, I have done somthing like this before....

Regards,
---Pinal
0
 
LVL 14

Accepted Solution

by:
Renante Entera earned 250 total points
ID: 11938390
Hello 451ls!

I think you need your code into something like this :

<cfparam name="searchList" default="">

<!--- First you will get all records --->
<cfquery name="GetRecord" datasource="#AppDataBase#">
  SELECT * FROM Table
</cfquery>

<!--- Set a list of all values of your ColumnA
<cfset dbList = ValueList(GetRecord.ColumnA)>
<cfset string = 'foo24'>

<!--- Loop over the List of ColumnA values --->
<cfloop list="#dbList#" index="i">
  <cfif FindNoCase(i,string)> <!--- You can use Find() function if you want to perform a case-sensitive search. --->
    <cfset searchList = ListAppend(searchList,i)>  <!--- If value is found then append value to the searchList --->
  </cfif>
</cfloop>

<!--- If searchList has value/s.  You will proceed to refer to your table again with conditional statement. --->
<cfif Len(searchList)>
  <cfquery name="GetFinalRecord" datasource="#AppDataBase#">
    SELECT * FROM Table
    <!--- You will use ListQualify() function if ColumnA datatype is string if not string simply use in (#searchList#) --->
    WHERE ColumnA in (#ListQualify(searchList,"'",",","ALL")#)
  </cfquery>

  <!--- Then finally, display the resultsets of your query. --->
  <cfoutput query="GetFinalRecord">
    #currentrow#. #ColumnA#<br>
  </cfoutput>
</cfif>

Just try this one.


Regards!
eNTRANCE2002 :-)
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11938470
I think entrance is close and on the right track except that you don't want the values of column a but the column  names.

so modify his code as follows :

<cfparam name="searchList" default="">

<!--- First you will get all records --->
<cfquery name="GetRecord" datasource="#AppDataBase#">
  SELECT * FROM Table
</cfquery>

<cfset dblist = GetRecord.ColumnList>
<cfset string = "FOO"> <!--- note uppercase is important or you can lowercase all objects later or use the findnocase --->

<cfloop list="#dbList#" index="i">
  <cfif FindNoCase(i,string)> <!--- You can use Find() function if you want to perform a case-sensitive search. --->
    <cfset searchList = ListAppend(searchList,i)>  <!--- If value is found then append value to the searchList --->
  </cfif>
</cfloop>

now is where it varies a bit because you want to select the column names that match

<cfif Len(searchList) neq 0>
  <cfquery name="GetFinalRecords" datasource="#AppDataBase#">
    SELECT #searchlist# FROM Table
  </cfquery>
</cfif>

<cfdump var="#GetFinalRecords#">


This should get you

SELECT foo24, foo, foo54, foobar, myfoo FROM Table

Hope that helps


0
 

Author Comment

by:451ls
ID: 11939058
Hello MRichmon,
Your answers have always been so right on that I am reluctant to challenge your assumption. But possibly I mislead you. I am wanting the records where the VALUE of ColumnA is found in the #string# variables, not the Column names. So if I understand the approach that Entrance2002 has provided, I think it is correct. I will adapt it and see if it works and let you both know.
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 11939117
To mrichmon:
  I think it is clearly stated by 451ls about the details on the problem.  In fact, I will have to analyze first the posted problem before I attempt to post any solutions to avoid any major mistake.

To 451ls:
  Just try it!  Perhaps that would resolved your problem.


Best wishes!
eNTRANCE2002 :-)

0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11942860
451ls , you are correct.  I misunderstood.

But if that is the case entrance's solution should work, but so should

SELECT * from Table WHERE ColumnA LIKE '#string#%'

which you originally posted.

So I am not sure why you need a more complex solution, but if you do then entrances's should work.
0
 

Author Comment

by:451ls
ID: 11943547
I am reluctant to use the complex solution as it is multiple queries and loops all because of a single extra character.

But again, tell me how this query would work if the #string# value is "foox" where x is any number and
the ColumnA value is "foo". If you can think of a way to come up with a ColumnA value that would equate for all possible values of "foo+", that would be the solution I am looking for.
Can I put a regular expression in the ColumnA value?
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 50 total points
ID: 11944418
If column A has values like

foo2
foo
myfoo
testing
jing
foo45

Then the statement SELECT * FROM mytable WHERE ColumnA LIKE 'foo%'
will return

foo2
foo
foo45


of course if you had foobar if would also return that.  It isn't restricted to numbers.  TO get that you would need entrance's two step solution.
0
 

Author Comment

by:451ls
ID: 11945969
Problem is I want to return only the column value of "foo" where the value of the #string# will always be "foosomething" (e.g. foo1, foo45, foobar, etc.).

I wanted to know what value I can but in the DB's ColumnA to match with strings that always begin with something, such as "foo". I can't believe there isn't a simpler way to do this.

It could be handled with a construct such as Pinaldave hints at, but never follows up on:

SELECT * FROM mytable WHERE ColumnA IS CONTAINED IN #string#

or

SELECT * FROM mytable WHERE [ColumnA & "%"] LIKE #string#.

But SQL just doesn't support that.

To be fair, I am awarding the original points to Entrance2002's solution even though I will code around
the solution with a <cfif> to detect this one variable string situation and allow the simple query
of SELECT * FROM mytable where ColumnA = #string# for all the others where the string is known
to be static.

Thanks for your attempts to clarify.

0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11946138
If you can accept things like foobar or foosomething then

SELECT * FROM mytable where ColumnA LIKE '#string#%'

Will work.
0
 

Author Comment

by:451ls
ID: 11946722
I respectfully disagree. It is backwards, as I claim in my original email.

If a mytable record contains a ColumnA value of "foo" and #string# is any of foo1, foo25, foobar,
this query will return no results.

SELECT * FROM mytable where ColumnA LIKE '#string#%'

My requirement is that it return the records where ColumnA is foo where #string#
is any of these values.
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11947819
Okay so you said that you know the first part of the string? I get that from this statement you made :

#string# will vary only in the last character which can be any number.

If so then do a left operation

SELECT * FROM mytable where ColumnA LIKE '#Left(string, 3)#%'

(in this case 3 since "foo" the part you are looking for is 3 characters long.

Or for a general case if only the last character will change

SELECT * FROM mytable where ColumnA LIKE '#Left(string, LEN(string) - 1)#%'
0
 

Author Comment

by:451ls
ID: 11947904
Ah, wonderful - that is EXACTLY what I was looking for (the general case of the last few characters).

How can I award you points for this - I guess I acted too quickly, my apologies.

 
0
 
LVL 35

Expert Comment

by:mrichmon
ID: 11948412
Points aren't important to me, just taht you get the answer you need :o)
0

Featured Post

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Join & Write a Comment

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now