Solved

SQL Query where left operator is contained in right operator?

Posted on 2004-08-30
14
199 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

809 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