Solved

Need MySQL string function

Posted on 2011-09-13
3
473 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

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 article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

635 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