To retrieve the strings starting with numeric digits.

[ganesh_g1982]

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

[wickning1]

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

[ganesh_g1982]

[!--quoteo(post=354420:date=Mar 13 2006, 11:00 AM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 13 2006, 11:00 AM) [snapback]354420[/snapback][/div][div class=\'quotemain\'][!--quotec--]
First question: You can use the REGEX comparison:
[code]SELECT * FROM company WHERE name REGEX '[[:digit:]].*'[/code]
Second question: Use the MATCH AGAINST syntax, e.g.
[code]SELECT * FROM company WHERE MATCH(name) AGAINST ('userinput')[/code]
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

[wickning1]

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%'"

[fenway]

Don't you need a FULLTEXT index to use MATCH... AGAINST?

[ganesh_g1982]

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.

[wickning1]

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

[ganesh_g1982]

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

[fenway]

What do you mean by together? You mean match ALL of the words, or match them as a phrase?