Solved

Need MySQL string function

Posted on 2011-09-13
3
461 Views
Last Modified: 2012-05-12
Experts,

new to mySQL.

i'm using the following function on a string in vb.net:

 Public Function subStringCount(ByVal fullString As String, ByVal subString As String) As Integer
       
        Dim r As New Regex(subString)
        Dim mc As MatchCollection = r.Matches(fullString)

        Return mc.Count

    End Function

i'm calling it like this:

    subStringCount(strGameHistory, "ET")

It takes a string and counts the occurance of a subset of characters within the string.

my data can look like this: "S0ETS2ETS5C0ETS2C0ETS1C1ET"

Is it possible to have this operation done within the where clause of my select? I'd rather not have to pull all the data back before performing this operation. so, something like:

select game_history from table1 where function_counting_occurance_subset_of_char(game_history, 'ET') = 5;

TIA,

crafuse
 
0
Comment
Question by:crafuse
  • 2
3 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 36534021
I think this technique should kind of work, but it's a little hacky:
http://pisceansheart.wordpress.com/2008/04/15/count-occurrence-of-character-in-a-string-using-mysql/

Which would give this I believe:
select game_history from table1 where ( (LENGTH(game_history) - LENGTH(REPLACE(game_history, 'ET', '')))/LENGTH('ET') ) = 5;
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 36534042
Having a bit more of a look around the web, it appears that's the best way to do it.
0
 

Author Closing Comment

by:crafuse
ID: 36534064
this is a perfect example of why this site can be pretty amazing!

great answer, works like a charm, thanks!
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question