Jump to content

like operator


jojay
 Share

Recommended Posts

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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.

Link to comment
Share on other sites

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 by Dan The Prof
Link to comment
Share on other sites

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 ;

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

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.....
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...