jojay Posted May 16, 2006 Share Posted May 16, 2006 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 More sharing options...
Little Goat Posted May 16, 2006 Share Posted May 16, 2006 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted May 16, 2006 Share Posted May 16, 2006 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 More sharing options...
pulpfiction Posted May 16, 2006 Share Posted May 16, 2006 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 More sharing options...
justsomeguy Posted May 16, 2006 Share Posted May 16, 2006 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 More sharing options...
pulpfiction Posted May 16, 2006 Share Posted May 16, 2006 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.. Link to comment Share on other sites More sharing options...
jojay Posted May 17, 2006 Author Share Posted May 17, 2006 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. Link to comment Share on other sites More sharing options...
justsomeguy Posted May 17, 2006 Share Posted May 17, 2006 Using LIKE is the best way. All you need to do is tell it what you are searching for. Link to comment Share on other sites More sharing options...
Jack McKalling Posted May 17, 2006 Share Posted May 17, 2006 (edited) 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 Link to comment Share on other sites More sharing options...
Kosher Kid Posted May 17, 2006 Share Posted May 17, 2006 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 More sharing options...
Jack McKalling Posted May 17, 2006 Share Posted May 17, 2006 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 More sharing options...
Kosher Kid Posted May 17, 2006 Share Posted May 17, 2006 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 Link to comment Share on other sites More sharing options...
jojay Posted May 17, 2006 Author Share Posted May 17, 2006 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..... Link to comment Share on other sites More sharing options...
Jack McKalling Posted May 17, 2006 Share Posted May 17, 2006 What exactly do you want then? Link to comment Share on other sites More sharing options...
jojay Posted May 17, 2006 Author Share Posted May 17, 2006 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now