Jump to content

To retrieve the strings starting with numeric digits.


ganesh_g1982

Recommended Posts

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

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

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

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%'"
Link to comment
Share on other sites

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

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.