homepage Welcome to WebmasterWorld Guest from 23.20.61.85
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
search with or without hyphen
search with or without hyphen
fahad direct



 
Msg#: 4188749 posted 5:58 pm on Aug 18, 2010 (gmt 0)

Hi,
I want to do a pattern matching like i want to search abc-def from my database where as in my where clause i have abcdef and i can't want use like command abc%def.

 

LifeinAsia

WebmasterWorld Administrator lifeinasia us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 4188749 posted 6:09 pm on Aug 18, 2010 (gmt 0)

You'll probably need to use regular expressions. I have very limited experience with regular expressions, so hopefully someone with more experience can suggest the exact format to try.

fahad direct



 
Msg#: 4188749 posted 6:44 pm on Aug 18, 2010 (gmt 0)

I am trying but unable if some can help me out.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4188749 posted 9:14 pm on Aug 18, 2010 (gmt 0)

select 'abc-def' regexp '^[[:alnum:]]+-*[[:alnum:]]+$';
--> 1 (true)

select 'ABC-DEF' regexp '^[[:alnum:]]+-*[[:alnum:]]+$';
--> 1

select 'abcdef' regexp '^[[:alnum:]]+-*[[:alnum:]]+$';
--> 1

select '@#-def' regexp '^[[:alnum:]]+-*[[:alnum:]]+$';
--> 0 (false)

^ = string starts with

[[:alnum:]] = alphanumeric character class, case insensitive

+ = one or more of the preceding

* = zero or more of the preceding, making the - optional

$ end of string, preceded by a rinse/repeat of alnum

mySQL regexps [dev.mysql.com]

fahad direct



 
Msg#: 4188749 posted 1:05 am on Aug 19, 2010 (gmt 0)

Thanks for the helpful reply, could you please help me to make a select statement to search abc-def by using abcdef in where clause, please correct me if I am wrong:

select field from table where field like 'abcdef' regexp '^[[:alnum:]]+-*[[:alnum:]]+$';

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4188749 posted 4:39 pm on Aug 19, 2010 (gmt 0)

I don't think you're going to be able to do that. The regexp must match the data, not the other way around, and you're asking to insert a character where there isn't one in the string, and it really can be anywhere. You could preprocess the input so it's like this, and do a long series of "or's":

where field = 'a-bcdef' or
field = 'ab-cdef' or
field = 'abc-def' ....

but that would be extremely unwieldy. You should probably re-think your front end, like

<p>Enter your product code:</p>
<p><label for="code-prefix">Prefix:</label>
<input type="text" name="code-prefix" id="code-prefix">
<label for="code-suffix">Suffix:</label>
<input type="text" name="code-suffix" id="code-suffix"></p>

Then you can do

$term = $_POST['code-prefix'] . '-' . $term = $_POST['code-sufffix'];

.... where field='$term';

This would be faster, more efficient, and less error prone anyway.

whoisgregg

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4188749 posted 6:08 pm on Aug 19, 2010 (gmt 0)

You could create an additional column that has all hyphens removed from it.

UPDATE `table` SET `search_column` = REPLACE(`sku`, '-', '');

Then just run your queries against `search_column` instead of against `sku`. Whenever someone does an UPDATE/INSERT just make sure you are populating your `search_column` value properly. :)

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4188749 posted 3:45 pm on Aug 20, 2010 (gmt 0)

There you go . . . then if someone does the dash, you can either just preg_replace the dash on input or do . . . where dashfield='$val' or nondashfield='$val'.

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