Welcome to WebmasterWorld Guest from 18.104.22.168 , register , free tools , login , search , pro membership , help , library , announcements , recent posts , open posts Become a Pro Member
search with or without hyphen search with or without hyphen fahad direct 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.
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
I am trying but unable if some can help me out. rocknbil
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
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
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.
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
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'.