• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

SQL Query where left operator is contained in right operator?

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
451ls
Asked:
451ls
  • 6
  • 5
  • 2
  • +1
2 Solutions
 
pinaldaveCommented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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
 
mrichmonCommented:
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
451lsAuthor Commented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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
 
mrichmonCommented:
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
 
451lsAuthor Commented:
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
 
mrichmonCommented:
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
 
451lsAuthor Commented:
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
 
mrichmonCommented:
If you can accept things like foobar or foosomething then

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

Will work.
0
 
451lsAuthor Commented:
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
 
mrichmonCommented:
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
 
451lsAuthor Commented:
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
 
mrichmonCommented:
Points aren't important to me, just taht you get the answer you need :o)
0

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now