Jump to content


Photo

To retrieve the strings starting with numeric digits.


  • Please log in to reply
8 replies to this topic

#1 ganesh_g1982

ganesh_g1982
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 13 March 2006 - 05:32 AM

Hi all,
Please help me to find a solution to this problme.

i am having a company database, and i m using php to retrive the data fromt he tables. i am able to search on the normal way like,

"select * from company where name like 'abcd%' order by name"

but i have an entry in the table, "123popular". i need to search and find this..Which query i shud use for this.

Alsi i would like to know, if the search string contails more than one word, what is the query to search each entry in the table matching for all the words entered by the user.

Please help me!!!. Tnx
Ganesh G Nair

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 13 March 2006 - 07:00 AM

First question: You can use the REGEX comparison:
SELECT * FROM company WHERE name REGEX '[[:digit:]].*'
Second question: Use the MATCH AGAINST syntax, e.g.
SELECT * FROM company WHERE MATCH(name) AGAINST ('userinput')
You'll probably want to create a fulltext index to make that fast.

#3 ganesh_g1982

ganesh_g1982
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 13 March 2006 - 10:30 AM

[!--quoteo(post=354420:date=Mar 13 2006, 11:00 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 13 2006, 11:00 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
First question: You can use the REGEX comparison:
SELECT * FROM company WHERE name REGEX '[[:digit:]].*'
Second question: Use the MATCH AGAINST syntax, e.g.
SELECT * FROM company WHERE MATCH(name) AGAINST ('userinput')
You'll probably want to create a fulltext index to make that fast.
[/quote]

First one its showing errors. i wish to check the names starting not only wiht one digit, 0-9. And the code you gave, i tried, its not running. error.
And the second its working, but another problem also came. when i search part of a word(eg. comp) it will not display anyhting. But if i search with computer, it will display. is there any possibilie to use % % around the search string so that even i type "c", it will display computers,catridges,cameras etc. like that.

Please help me on solving this. and i wish you explain the first once again. Thank you very much.

Ganesh G Nair

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 13 March 2006 - 02:20 PM

I thought you wanted things that started with any number. If you want to search for specific numbers, that's exactly the same as with letters. For 1, "SELECT * FROM company WHERE name LIKE '1%'".

The MATCH AGAINST syntax only searches for whole words, if you just wanted a substring search, just keep using LIKE: "SELECT * FROM company WHERE name LIKE '%userinput%'"

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 March 2006 - 07:42 PM

Don't you need a FULLTEXT index to use MATCH... AGAINST?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#6 ganesh_g1982

ganesh_g1982
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 14 March 2006 - 05:41 AM

hi,

no, i want to retrieve all the streing starting with the numeric digits. but the first syntax is not working.
ie, SELECT * FROM company WHERE name REGEX '[[:digit:]].*'

do i need to modify anyhting on inside '' (after REGEX). i just changed the table name and tried. it is not working and error is,
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REGEX '[[:digit:]].*'
LIMIT 0, 30' at line 1

And for the other, i wish to get a result even when a user enters a part of a word, or more than one word. Ofcourse a good search query which can take out results if there any type of a match with the search string.


please advice me.


#7 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 14 March 2006 - 02:16 PM

Sorry, typo. It's REGEXP, not REGEX. Stupid SQL monkeys.

#8 ganesh_g1982

ganesh_g1982
  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 15 March 2006 - 04:43 AM

hi,
thanks its working. now i am able to query all string which contains numbers on it.

I would like to get the assistance for the search query also. i do like to follow with like ('%') operator because on my website , there are more chances to search by characters.not full words. so if you use match against query it may not work. But still i wud like to know if the users enter 2-3 words together, how we could check it with each database entires?

Any solutions?

Help!!!!!

Thanks
Ganesh G Nair


#9 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 March 2006 - 05:35 PM

What do you mean by together? You mean match ALL of the words, or match them as a phrase?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users