473,320 Members | 1,870 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Using REGEXP in searching

9
Anyone having experience in MySQL and REGEXP ?

I have 1 input field, "what"

Let's say I have the input value "e-mail" and the database field contains the value "email". Is there some way to put some variants into the expression to match values even if the "-" exists or not in the database.

Or is it possible using the LIKE clause for such operations?

Any examples is welcome.

Example code

what = Request.Form("what")
WHERE searchwords REGEXP '[[:<:]]"&what&"'"
Mar 1 '08 #1
4 1638
mwasif
802 Expert 512MB
Use ? to make a character optional e.g.
Expand|Select|Wrap|Line Numbers
  1. WHERE searchwords REGEXP 'e-?mail'
You need to find and replace - with -? in search string.
Mar 3 '08 #2
TobbeK
9
Hi and thanks for the reply

Can the search and replacement be done by the REGEXP or do I need an function taking care of that before the query. The hyphen is the only special character allowed in strings so the replacement from "-" to "-?" can easy be done, but the question is the matching problem. Is it possible to compare just the letters from the search input string with just the letters in values a database column.

Input ("e-mail")
db column ("email")
And reverse:
Input ("email")
db column("e-mail")

The string "e-mail" is just an example, it can can be any other words.

Use ? to make a character optional e.g.
Expand|Select|Wrap|Line Numbers
  1. WHERE searchwords REGEXP 'e-?mail'
You need to find and replace - with -? in search string.
Mar 3 '08 #3
mwasif
802 Expert 512MB
Can the search and replacement be done by the REGEXP or do I need an function taking care of that before the query.
Yes, you need a function to replace. You can do it in your scripting language or use a MySQL function REPLACE() with in the query.

And reverse:
Input ("email")
db column("e-mail")

The string "e-mail" is just an example, it can can be any other words.
Where will you put '-' in an input string? Is there any rule? This will be a resource intesive procedure, '-' can be placed in many places in email like
e-mail
em-ail
ema-il
emai-l

If you are providing a search facility, then take a look at FULLTEXT search.
Mar 3 '08 #4
TobbeK
9
Hi there!

Yes It seems to be very tricky :-)

I have checked the possibuility to use FULLTEXT search but I use InnoDB tables. That was the only way I could manage foreign keys. I'm not really sure how to rule the strings, one way might be using split funktions, but then I have the problem to find the matching position in the db column, dynamicly identify the position of a possible hyphens so I know where to start and stop. The results can be very odd doing that wrong.

I have checked your link and there was one user comment similar to my problem. The comment is provided below

"
------------------
Hyphen '-' characters break literals at the moment.
A search for something like "GATA-D22S690" finds
all entries containing GATA and not the full
hyphenated text. The '-' character is treated as a
word stop even within literals. The same is true if
any of the special text search modifiers are used
(eg +, -, ~)
so that hyphenated literals are not correctly found with full text searches.
-------------------
"

Yes, you need a function to replace. You can do it in your scripting language or use a MySQL function REPLACE() with in the query.


Where will you put '-' in an input string? Is there any rule? This will be a resource intesive procedure, '-' can be placed in many places in email like
e-mail
em-ail
ema-il
emai-l

If you are providing a search facility, then take a look at FULLTEXT search.
Mar 3 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: David | last post by:
Hi, I've had a search through google but couldn't really find the answer I was looking for.I'm new to PHP, so please take it <relatively> easy. I've created a script which runs some SNMP...
10
by: Anand Pillai | last post by:
To search a word in a group of words, say a paragraph or a web page, would a string search or a regexp search be faster? The string search would of course be, if str.find(substr) != -1:...
6
by: Rizyak | last post by:
******************** alt.php.sql,comp databases.ms-sqlserver microsoft.public.sqlserver.programming *********************************** Why doesn't this work: SELECT * FROM 'Events'
3
by: Sped Erstad | last post by:
There must be a simple regexp reason for this little question but it's driving me nuts. Below is a simple regexp to determine if a string contains only numbers. I'm running these two strings...
7
by: Csaba Gabor | last post by:
I need to come up with a function function regExpPos (text, re, parenNum) { ... } that will return the position within text of RegExp.$parenNum if there is a match, and -1 otherwise. For...
4
by: conan | last post by:
This regexp '<widget class=".*" id=".*">' works well with 'grep' for matching lines of the kind <widget class="GtkWindow" id="window1"> on a XML .glade file However that's not true for the...
9
by: vbfoobar | last post by:
Hello I am looking for python code that takes as input a list of strings (most similar, but not necessarily, and rather short: say not longer than 50 chars) and that computes and outputs the...
2
by: silly putty | last post by:
hello. this is all the code in my javascript. there's nothing else in the html page except for the following: var regex = new RegExp("\|A\|"); if (regex.test("8_2006|13|15|31|"))...
2
by: ahgan | last post by:
Hi I'm new here. I'm trying to put some code to copy files with specific patterns/keywords from a target location. I encountered a pattern matching (regexp) issue where the filename didn't...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.