I'm doing some research in a MySQL database where some data is stored as XML. I already managed to find the string I was searching for:
select * from foo where Concat(foo) like '%bar%';
Now I'm trying to find only entries where "bar" appears 2 times. In the table where I'm searching "bar" always appears once so I want to find the entries with at least 2x "bar".
Can you give me some advice?
You should use the REGEXP method
select * from foo where Concat(foo) regexp '(bar).*(bar)';
Breakdown
()- First capturing group
bar - The expression to be captured
. - Matches any character
* - Matches zero or more of a character
(bar) - Second capturing group
https://regex101.com/r/wM3wX9/1
From the MySQL documentation
Performs a pattern match of a string expression expr against a pattern pat. The pattern can be an extended regular expression, the syntax for which is discussed later in this section. Returns 1 if expr matches pat; otherwise it returns 0. If either expr or pat is NULL, the result is NULL. RLIKE is a synonym for REGEXP, provided for mSQL compatibility.
I also created an SQL Fiddle for this.
Here is a scalable solution, which can easily used on any repeated times, such as 1, 2, 10, 100 and etc.
-- match twice
select * from foo where (length(foo) - replace(foo,'bar',''))/length('bar') = 2;
-- match 3 repeated times
select * from foo where (length(foo) - replace(foo,'bar',''))/length('bar') = 3;
-- match 100 repeated times
select * from foo where (length(foo) - replace(foo,'bar',''))/length('bar') = 100;
SQL function to get count of how many times string appears in column?
