like operator

[jojay]

Hi, Can someone please help with the following syntax:

sql1 = "Select Cname from page1 where Cname like '%Cname%'"

Above code isnt working. Cname is a text field and would like to know the syntax for the above sql statement using Like. Thanks.

[Little Goat]

that looks good to me, with what small knowledge of sql that I have.by the way, in the future, post topics in the appropriate forums.LG

[justsomeguy]

That syntax is correct, it will find all records where the field Cname contains the text "Cname". It will find all of these:Cname123Cname123asdf asdf asdf asdf asdf Cname asdf asdf asdf asdf asdfpoiuytrewqCnameBut it will not find this:Cn ame

[pulpfiction]

I guess the problem is in the LIKE you have given a Column name [Cname], this will not work. We have to give some text of the data under this column name. Select Cname from page1 where Cname like '%Cname%'Select Cname from page1 where Cname like '%ja%'This will look in the column "Cname" for the text ......ja......cname---------jojaypulufictionname3name4ex: match would be jojay

[justsomeguy]

Still though, his syntax is correct, he just might not be saying the right thing. Right now the original query searches for the text "Cname", if he wants to search for any other text then he needs to include that in the query. But still, the syntax is correct. The semantics might be missing though.

[pulpfiction]

i thought that what jojay meant was that he was not getting any result..yeah the syntax is correct. jojay- you may wanna check other parts of the code..

[jojay]

i thought that what jojay meant was that he was not getting any result..yeah the syntax is correct. jojay- you may wanna check other parts of the code..

<{POST_SNAPBACK}>

cname is the field name in the database but I dont want to give any particular letters such '%ja%' because there can be numerous combination in the field "cname".Now, when I use the above code, it result is:

select * from page1 where cname like '%cname%'

or what is the best way to search a field name? I have done in the paste and dont remember now.

[justsomeguy]

Using LIKE is the best way. All you need to do is tell it what you are searching for.

[Jack McKalling]

Or when you know exactly what you are looking for, this also works:

SELECT * FROM page1 WHERE cname='something'

(I prefer sql code in uppercase, but its not mandatory)There are more ways, and also more posible results combined in one query, with a little help of the 'AND' instruction, but you could find it all in the tutorial. If you want to know exactly what signs and marks you can use inside the LIKE clause, then goto MySQL.com

Edited May 17, 2006 by Dan The Prof

[Kosher Kid]

jojay--are you just trying to select all records from the cname field in the page1 table?if so, the correct sql is:SELECT cname FROM page1; [i agree with profdan that CAPS is the proper way to write an sql query.]if you want the entire table:SELECT * FROM page1;if you want selected cname records,SELECT * FROM page1 WHERE cname LIKE [see below]; or WHERE cname = "string";When using wildcards with the like phrase, use * rather than %:SELECT cname FROM page1 where cname LIKE "*want*"use only the * as needed...if you want something that begins with a certain string then write LIKE "joe*"....for something that ends with a certain string, write LIKE "*joe"...for something that contains a certain string, write LIKE "*joe*"also, a sql query must end in a ;

[Jack McKalling]

Actually, the %-wildcard counts for MySQL 4, and has the same meaning as the *-sign, which maybe has been added in later versions, I don't know. So the % sign may be used instead of the *, if someone confirms it has tod o with the MySQL version.And maybe that is also why you think SQL needs a semicolon at the end of the query, which is not true for MySQL version whatever, but that you use a different database which does need it :)So remember, a semicolon (;) is not always neccesary in SQL, and a % sign inside the LIKE phrase is also allowed as wildcard

[Kosher Kid]

thanks for the education...i have not worked with the mysql database and have only dealt with access, sql server and the like, which do use the * wildcard and require a semicolon...getting familiar with mysql is on my long list of things to do

[jojay]

Actually, the %-wildcard counts for MySQL 4, and has the same meaning as the *-sign, which maybe has been added in later versions, I don't know. So the % sign may be used instead of the *, if someone confirms it has tod o with the MySQL version.And maybe that is also why you think SQL needs a semicolon at the end of the query, which is not true for MySQL version whatever, but that you use a different database which does need it :)So remember, a semicolon (;) is not always neccesary in SQL, and a % sign inside the LIKE phrase is also allowed as wildcard

<{POST_SNAPBACK}>

Thank you all. I am using MS Access database and using the sql code in asp and it doesnt allow a semicolon, it gives an error. I am using this sql code to search the values of a field in the database, and I dont want to get to specific resultsets such as "ja*" or "*ja". Should I create a variable for Clname(field name in the database) then use it in the code? Not yet there.....

[Jack McKalling]

What exactly do you want then?

[jojay]

What exactly do you want then?

<{POST_SNAPBACK}>

Its okay. I got it. I was looking for this syntax to search values in a field. Below code works great:

SELECT  Clname from Cform WHERE Clname  like '%" &Clname& "%'

Thank you all for your contribution. It will surely be helpful.