substr_count MySQL UDF
by Sorin Neacsu <sorin (at) tsc (dot) ro>

Compilation instructions :

The easiest way is to copy the substr_count.cc file to the sql directory of the mysql source.
After that, run the following commands :

1:
gcc -shared -o substr_count.so substr_count.cc -I MYSQL_INCLUDE_DIR
Comment : replace MYSQL_INCLUDE_DIR with your MySQL include dir

2:
make substr_count.o

3:
cp substr_count.so /usr/lib
Comment : you can copy this wherever you want in the LD path

4:
Run this query :
CREATE FUNCTION substr_count RETURNS INT SONAME "substr_count.so";

5:
Run this query to test it :
SELECT SUBSTR_COUNT('test test', 'test');
It should return 2

단순히 해당 string 이 몇개나 들어있는지를 알려준다.

MySQL UDF 예제로서 활용해 보기 바란다.

--------------------------------------------------------------------

/*
MySQL UDF : substr_count
by Sorin Neacsu <sorin (at) tsc (dot) ro>
*/
#include <stdio.h>
#include <string.h>
#include <my_global.h>
#include <my_sys.h>
#include <mysql.h>

extern "C" {
  my_bool substr_count_init (UDF_INIT *initid, UDF_ARGS *args, char *message);
  long long substr_count(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error);
}

my_bool substr_count_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
  if ( (args->arg_count != 2) || (args->arg_type[0] != STRING_RESULT) || (args->arg_type[1] != STRING_RESULT) || !strlen(args->args[0]) || !strlen(args->args[1]))
  {
    strcpy(message, "substr_count must have exactly two arguments, both not null strings");
    return 1;
  } else {
    return 0;
  };
}

long long substr_count(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
  char *str = (char *)malloc(1024);
  int i=0;        

  strcpy(str, args->args[0]);
  while ( str = (char *)strstr(str, args->args[1]) )
  { 
    str += strlen(args->args[1]);
    i++;
  };

  free(str);
  return i;
}