This week's book giveaway is in the Design forum.
We're giving away four copies of Building Microservices and have Sam Newman on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes number of times a substring met in a string Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Building Microservices this week in the Design forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "number of times a substring met in a string" Watch "number of times a substring met in a string" New topic

number of times a substring met in a string

Asher Tarnopolski
Ranch Hand

Joined: Jul 28, 2001
Posts: 260
i wanna run on mysql table a method which will return the
entries where a substring is met a wanted number of times in a varchar type field.
for example, let's say there are 4 entries, which varchar field look like this :
| a |
| aaa |
| abacad |
| bagafaca |
i wanna get all lines where "a" is met 3 times, so second and third lines only will be returned.
i didn't find any mysql function which seems to be helpful, may be you have ideas about it?

Asher Tarnopolski
Joe Ess

Joined: Oct 29, 2001
Posts: 9138

SQL LIKE might get you what you want, but you will probably have to do some extra work to weed out the strings that contain more than the given number of substrings.

[How To Ask Questions On JavaRanch]
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 32507

Joe is right about the like. If you use two like statements, you can get what you are looking for. Keep in mind that this query is probably slow.
select * from table where field like "%a%a%a%"
and field not like "%a%a%a%a%"

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link:
subject: number of times a substring met in a string
It's not a secret anymore!