Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need MySQL string function

Posted on 2011-09-13
3
Medium Priority
?
482 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 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month12 days, 12 hours left to enroll

971 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