maria-discuss team mailing list archive

[Federico Razzoli <federico_raz@xxxxxxxx>]

I agree with all Paul Larsen's thoughts about stored programs.

@Roberto, my personal opinion:

If you you are counting the occurrences of ONE char, you can use this expression:

LENGTH(col_name) - LENGTH(REPLACE(col_name, 'a', ''))

C functions, even when used in a stupid way (like in this case), are probably much faster than stored functions. If you always search for the same char, maybe you can use a persistent column.

Regards,
Federico


--------------------------------------------
Mer 19/3/14, Peter Laursen <peter_laursen@xxxxxxxxxx> ha scritto:

 Oggetto: Re: [Maria-discuss] help - i'm not finding one sql function
 A: "Reindl Harald" <h.reindl@xxxxxxxxxxxxx>
 Cc: "Maria Discuss" <maria-discuss@xxxxxxxxxxxxxxxxxxx>
 Data: Mercoledì 19 marzo 2014, 16:44
 
 @Roberto ..
 write a Stored Function. You can do that with HeidiSQL (or
 whatever available client). It may be done basically as a
 loop doing something like
 * checking for the substring from 1st character -
 increment count if substring is found 
 
 
 * TRIM the 1st/leftmost character
 away.. and repeat till end of string is
 reached.(I don't claim this will be high
 performing, and there are probably many ways to do
 it)
 
 
 But now consider you have the string
 'abbbbbbc' and want to count for occurrences of
 'bb'.  How many will/shall you find? 3 or 5? This
 is somewhat ambigious in this case actually, and only you
 can decide how such function should work to fit your
 needs.
 
 
 -- Peter
 
 
 On Wed, Mar 19, 2014
 at 4:22 PM, Reindl Harald <h.reindl@xxxxxxxxxxxxx>
 wrote:
 
 
 
 
 
 Am 19.03.2014 16:04, schrieb Roberto Spadim:
 
 > yes, but i have a table with
 1M rows, and i don't have php/c/python/perl here, just
 heidisql
 
 > any help is wellcome
 
 >
 
 > 2014-03-19 12:03 GMT-03:00 Reindl Harald <h.reindl@xxxxxxxxxxxxx
 <mailto:h.reindl@xxxxxxxxxxxxx>>:
 
 >
 
 >     Am 19.03.2014 15:51, schrieb Roberto Spadim:
 
 >     > hi guys, i'm not finding a function to
 return how many character i have, for example:
 
 >     >
 
 >     > "banana"
 
 >     >
 
 >     > i want a function that return 2
 "n" characters, example:
 
 >     >
 substr_count("banana","n") => 2
 
 >     >
 
 >     > sorry it a begginners question, but i
 didn't found it in mysql/mariadb manual
 
 >
 
 >     that is not the job of the database server
 because it
 
 >     can't use indexes for such things - just
 iterate
 
 >     the result and do it in the application
 
 
 
 it would be so much easier if
 
 
 
  * reply only to the list so the next reply get not
 off-list
 
  * don't top post
 
  * dont reply in HTML to plaintext posts
 
 
 
 these are the string functions
 
 https://mariadb.com/kb/en/string-functions/
 
 
 
 * the database would not be faster as a script
 
 * frankly the opposite may be true http://bugs.mysql.com/bug.php?id=59253
 
 * there is no function like substr_count
 
 * just install PHP or whatever
 
 
 
 there is not much to help
 
 
 
 
 _______________________________________________
 
 Mailing list: https://launchpad.net/~maria-discuss
 
 Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
 
 Unsubscribe : https://launchpad.net/~maria-discuss
 
 More help   : https://help.launchpad.net/ListHelp
 
 
 
 
 -----Segue allegato-----
 
 _______________________________________________
 Mailing list: https://launchpad.net/~maria-discuss
 Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
 Unsubscribe : https://launchpad.net/~maria-discuss
 More help   : https://help.launchpad.net/ListHelp