451ls
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
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.
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 :-)
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 :-)
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.
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.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
If you can accept things like foobar or foosomething then
SELECT * FROM mytable where ColumnA LIKE '#string#%'
Will work.
SELECT * FROM mytable where ColumnA LIKE '#string#%'
Will work.
ASKER
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.
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.
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)#%'
#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)#%'
ASKER
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.
How can I award you points for this - I guess I acted too quickly, my apologies.
Points aren't important to me, just taht you get the answer you need :o)
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