Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query where left operator is contained in right operator?

Posted on 2004-08-30
14
Medium Priority
?
204 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
[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
  • 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 1000 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 200 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

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

Question has a verified solution.

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

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…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses

730 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