• Post Reply Bookmark Topic Watch Topic
  • New Topic

number of times a substring met in a string

 
Asher Tarnopolski
Ranch Hand
Posts: 260
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,
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?
thanx
 
Joe Ess
Bartender
Posts: 9370
11
Linux Mac OS X Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Sheriff
Posts: 36007
422
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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%"
 
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!