My case is as below.
I have below records in a mysql table in a "productname" column.
Adults-Work-Blue-Green-Pink-Ghagra-Choli-Dupatta
Afro-Party-Retro-Wigs-Hair-Black-Curly-Gabbar
Now I want to find record which starts with A and then after first hyphen W.
If I write query as below,
select DISTINCT Item_Code, Item_Name
from tbl_stock_detail
where Item_Name like 'A%-W%'
It shows me both records. I want to see only first record here as after A, W is the first letter after Hyphen. Similarly If I make it like 'A%-W%-B%', I want it to show only first record as after 'A', 'W' is the first letter after first hyphen and 'B' is the first letter after second hyphen. Just remember, there are 1000s of records with variety of names, so we cant check the string length.
Can I write such query in mysql which check the sequence as well?
Sorry I am bad at Regex . This might be possible with regex.
Please advise.
Edited Question below :
I have a record like this "Long Skirt red sequin tikli dance" Now client says, he want to search based on these 3 characters only, R : Red T : Tikli S : Skirt
Also second thing plz. If Skirt and Red has 2 spaces instead of 1, our old regex doesnt work. But it should work as they have mistakenly added multiple spaces between 2 words sometimes. can you plz help me to fulfill these 2 requirements?
More Editing :
This works static but it gives me some escape sequence problem in PHP. Below is my php code,
<?php
$var = 'SRT';
for($i=0;$i<strlen($var);$i++)
{
$term = '^'.$var[$i].'|[- ]'.$var[$i];
if($i == 0)
$subquery0 .= " where Item_Name REGEXP '".$term."'";
else
$subquery0 .= " or Item_Name REGEXP '".$term."'";
}
?>
When I echo $term, it shows '^S only. But If I remove | symbol then it shows complete string . May be pipe symbol needs escape sequence I tried it as '//|' but it doesnt work. Any idea on this or I have to open a new question as this is a PHP question?
As hjpotter92 suggests in his comment, you should be able to use a regular expression to locate the rows with the specified matches.
A single LIKE predicate can't perform this type of matching. (It would take knowledge of the maximum length of each component ('Adults','Work', et al.) and a whole unwieldy boatload of LIKE predicates and'ed and or'd together for all the various possible lengths. We we do not want to go there.)
Regular expressions are nothing to be afraid of. Regular expressions can become fairly involved, but that's where they reveal their power and become very useful.
To match first character is an 'A' (either capital or lowercase), later followed by a hyphen, immediately following the first hyphen is a 'W', you could do something like this:
WHERE Item_Name REGEXP '^A[^-]*-W'
Similarly, starting with A, followed (some point later) by a hyphen, immediately following the next hyphen a 'W', followed (later) by another hyphen and a 'B'
WHERE Item_Name REGEXP '^A[^-]*-W[^-]*-B'
Let's unpack that last regular expression a bit, piece by piece:
^A
The first caret matches the beginning of the string, and the character A matches a character A (note: that's a case insensitive match on connections to my databases). So that says, "starting with A or a".
[^-]*
this part matches any number of characters which are not a hyphen.
-W
this part matches a hyphen immediately followed by a W. (Because of the immediately preceding part, this would be the first hyphen that occurs after the A that was previously matched.)
[^-]*
again, any number of characters (zero or more) which are not a hyphen character
-B
matches a hyphen immediately followed by a B. Again, this would be the first hyphen following the W that was matched.
And the remainder of the string can be anything.