SQL like statement

[DDhillon]

Where am I doing wrong here?

select company, womens from tblwshow where company LIKE '%things%'

I know that there is a text "things" in some of the company names but it is
not giving any output. I have used this code b4 too but I dont know where I m
doing wrong this time.

Thank you

[Aegis Kleais]

Can you do a select * and see if you get ANY records? Step by step troubleshooting. What do you mean by it's not giving any output? What does the query's RECORDCOUNT return?

[DDhillon]

Yes I have tried doing Select *. It is not showing any recordcount.

[paross1]

Could you possibly be dealing with a case sensitivity issue? Perhaps you can
use a (database dependent) function to guarantee that you are matching case,
such as....

WHERE LOWER(company) LIKE '%things%'
--or--
WHERE LCASE(company) LIKE '%things%'
..etc.

Phil

[DDhillon]

Let me elaborate my problem little more. Actually I m trying to fing the
specific word from the list of 50,000 companies. Lets say I want to find the
company that name contains "things". I m trying to use this statement Select *
from tblwshow where company like '%things%'. Do you think I m doing it right?

I hope this will explain more.

[basky]

You are doing it right. But as paross1 said is the DB case sensitive. What DB
are you using? What is the datatype of the company field?
Did you try paross1's solution?

Also make sure that there are some companies that has the string you are using
in the like constraint.

[BenDC]

For this kind of text search, verity is a much better tool and a much FASTER tool than a SQL like statement.... You can index the entire DB and search through it in miliseconds....

[DDhillon]

I m using MS Access. The field is memo type. I m copying and pasting the string
in statememnt. I have also tried using MS Access query view to see results. I
dont see anything when I run it.

SELECT * from tblwshow where company like '%AIRTIGER%'

The company name is "ATE AIRTIGER EXPRESS CANADA INC"

I think the query above should show some result.

[basky]

I don't think you can use like operation on memo field in access. I am pretty sure about it, but please double check it.

[basky]

May be you can use Verity to solve this issue, or change the field type to varchar or equivalent thing in access.

[DDhillon]

To make myself more clear. Let me put down the code here. I think it will help
in understand the problem better:

I m running a list loop that searches each item from the loop in the name of
companies. Once it finds that item, it updates another field by putting 1 in it.

<cfset
thetext='Health,fitness,medical,physical,mental,sp iritual,spa,rejuvenation,relat
ionship,knowledge,empowerment,dr,chiropractic,chir opractor,skin,massage,therapy,
therapeutic,women,woman,feel,good,fit,curve,body,p leasure,her,she,physio,psychol
ogy,psychological,book,bond,chapter,slim,acupunctu re,acupressure,tai,herb,herbal
,adult,power,sex,make,making,treatment,lather,bath ,dental,dentist,disorder,teach
ing,teach,eye,female,age,lady,ladies,aerobic,optic al,optician,publish,weight,ref
lex,reduce,control,sport,yoga'>


<CFLOOP From = "1" To = "#listLen(thetext)#" INDEX = "i">
<cfquery name="getdata" datasource="#mydsn.dsn#">
select id,company,womens from tblwshow where company like
'#listgetat(thetext,i)#'
</cfquery>
<cfoutput>
#listgetat(thetext,i)#
</cfoutput>

</cfloop>
<cfoutput query="getdata" maxrows="1000">
<cfquery name="updatedata" datasource="#mydsn.dsn#">
update tblwshow set womens='1' where id=#getdata.id#
</cfquery>
</cfoutput>
</cfloop>

[The ScareCrow]

You can't use a like statement on a memo field in ms access. I think you can't even use a memo field in the where clause


Ken

[DDhillon]

I have used like clause before in the memo foeld. Do you think I wrote the right code though?

[basky]

Ok I think I found the problem.

Use * instead of % signs. That let me use like on memo fields also. I tried
this from the access and could get that to work. Try it from a cfm page and let
us see how ODBC driver handles it.

So your query should look like

<cfquery name="getdata" datasource="#mydsn.dsn#">
select id,company,womens from tblwshow where company like
'*#listgetat(thetext,i)#*'
</cfquery>


Let us know if this has worked for you.

[The ScareCrow]

I stand corrected, I was sure I read it somewhere, anyway

basky, in access itself you use "*" when using a odbc driver you use "%"

But I think I found the problem, reserved words

Also there is no need to use the listgetat function

<cfloop index="idx" list="#thetext#">
<cfquery name="getdata" datasource="#mydsn.dsn#">
select [id],[company],[womens] from tblwshow where [company] like '#idx#'
</cfquery>
<cfoutput>
#idx#
</cfoutput>

Give that a try

Ken

[DDhillon]

Thank you for all your guys help but this time I dont know maybe its newer
version of CF things arent working for me. I tried putting '*' as a wild
character in the query while using query view in access. It works fine. When I
use the simple query written below in cf its not showing any result at all. It
looks very simple but I dont know why its not working.

<cfquery name="getdata" datasource="#apnadirectory.dsn#">
select id,company,womens from tblwshow where company like '*SPA*'
</cfquery>
<cfoutput query="getdata">
#company#<br><br>
</cfoutput>

[basky]

Did you try ScareCrow's suggestion. try using [ ] with % (if it does not work try * also)

[The ScareCrow]

If it works in access, create your query in access, test, then copy the query and paste into your cfquery tag, then see if this works. Note you will have to change the "*" for "%"

Ken

[Unregistered]

Try '%#things#%'

Where am I doing wrong here?

select company, womens from tblwshow where company LIKE '%things%'

I know that there is a text "things" in some of the company names but it is
not giving any output. I have used this code b4 too but I dont know where I m
doing wrong this time.

Thank you