Solved

Need MySQL string function

Posted on 2011-09-13
3
466 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

740 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