homepage Welcome to WebmasterWorld Guest from 107.22.78.233
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
telling mysql to ignore specific characters on select queries
moshebar




msg:3838907
 2:07 pm on Jan 31, 2009 (gmt 0)

hey,
i have a table with a varchar field that i want to run 'select' queries on .

the thing is, that some (maybe even half) of the values of this field may contain dots (".") in them, and while searching for a pattern, i want mysql to ignore the dots,regex-alike .

e.g:
when looking for "my name is john"
it will find the records with the field values :
"my name is john",
"my.name.is.john"

 

Morgenhund




msg:3840708
 10:56 pm on Feb 2, 2009 (gmt 0)

Using regex is slow, paintful and won't help.

What I can imagine, create an extra field in the table, with duplicate data, where all dots are replaced with spaces (unified).

Then, use that unified argument to perform searches.

So, for every f1 = "my name is john", there will be duplicate f2 = "my name is john", and for f1 = "my.name.is.john" duplicate will be f2 = "my name is john".

create table ZZZ (
f1 varchar(255),
f2 varchar(255)
);

insert into ZZZ (fa, f2) values (f1, preg_replace('/\./', ' ', $f2));

Then, "select * from ZZZ where f2 = 'my name is john'"

This method takes its amount of disk space, but is very fast (you can even use indexes).

And disk space is cheap today :-)

[edited by: Morgenhund at 11:02 pm (utc) on Feb. 2, 2009]

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved