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 mailto:h.reindl@thelounge.net>:

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

/) this worked nice, since i was only replacing one character =) no problem now i done the stored function, and executed it with 15 seconds, maybe a native function could execute it with ~3 seconds :) thanks guys 2014-03-19 12:44 GMT-03:00 Peter Laursen :

@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 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 >:

...

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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

I feel like to comment here on "because it can't use indexes for such things". This is not a serious reply IMO. It is just another way or saying "Please shut up!" How many existing string functions ( http://dev.mysql.com/doc/refman/5.6/en/string-functions.html) use an index? Does "select concat('a','b');" do for instance? I cannot think of any. And why do we have 'stored programs' if a database server should only do things that uses indexes? Does a WHILE-loop inside a stroed program use an index? Unlike most other RDBMS, MySQL has a tradition to do things in the application thet other RDBMS would do in the database. But that traditions is/was because of lack of functionality in the server before 5.0 (what is still the case in some respects. Try compare a MySQL TRIGGER with an Oracle or SQL Server same, for instance). That said: this functionality can be implemented in a stored function using existing string functions (or in the application, as sugggested) quite easily. It is not possible (or desirable) to add build-in functions for everything. And I have no clue is such string function exists in other RDBMS. -- Peter -- Webyog On Wed, Mar 19, 2014 at 4:03 PM, Reindl Harald wrote:

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

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

i know sqlite and mysql are very different, but at sqlite i can create `native` functions at mysql only with plugins since i like a lot mariadb, i think a plugin with many usefull functions could be enabled with some variable or some install plugin command that help a lot, some times for example the base64_encode/decode function was a verrrrry old feature that i reported to mysql maybe at 2004 or 2006, but only now (2013/2014) it`s inside mysql/mariadb code :( 2014-03-19 12:33 GMT-03:00 Reindl Harald :

i feel like to comment mailing-list basics

* DON'T top-post, you are destryoing threads * DON'T convert plaintext posts to HTML * DON'T reply-all, the reply button results in a off-list answer

...

I feel like to comment here on "because it can't use indexes for such

Am 19.03.2014 16:25, schrieb Peter Laursen: things".

...

This is not a serious reply IMO. It is just another way or saying "Please shut up!"

it is serious and don't insinuate words one have not said there is a) no such function and b) no benefit of it

...

How many existing string functions ( http://dev.mysql.com/doc/refman/5.6/en/string-functions.html) use an index? Does "select concat('a','b');" do for instance?

that does not read and seek anything in a column frankly "select concat('a','b');" is the same as "ab"

...

I cannot think of any. And why do we have 'stored programs' if a database server should only do things that uses indexes? Does a WHILE-loop inside a stroed program use an index?

surely can someone discuss what should be donme

...

Unlike most other RDBMS, MySQL has a tradition to do things in the application thet other RDBMS would do in the database. But that traditions is/was because of lack of functionality in the server before 5.0 (what is still the case in some respects. Try compare a MySQL TRIGGER with an Oracle or SQL Server same, for instance).

That said: this functionality can be implemented in a stored function using existing string functions (or in the application, as sugggested) quite easily

maybe

...

It is not possible (or desirable) to add build-in functions for everything

which was my point

...

And I have no clue is such string function exists in other RDBMS.

unlikely

...

On Wed, Mar 19, 2014 at 4:03 PM, Reindl Harald > wrote:

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

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

2014-03-19 12:25 GMT-03:00 Peter Laursen :

I feel like to comment here on "because it can't use indexes for such things". This is not a serious reply IMO. It is just another way or saying "Please shut up!"

yes

How many existing string functions ( http://dev.mysql.com/doc/refman/5.6/en/string-functions.html) use an index? Does "select concat('a','b');" do for instance? I cannot think of any. And why do we have 'stored programs' if a database server should only do things that uses indexes? Does a WHILE-loop inside a stroed program use an index?

Unlike most other RDBMS, MySQL has a tradition to do things in the application thet other RDBMS would do in the database. But that traditions is/was because of lack of functionality in the server before 5.0 (what is still the case in some respects. Try compare a MySQL TRIGGER with an Oracle or SQL Server same, for instance).

That said: this functionality can be implemented in a stored function using existing string functions (or in the application, as sugggested) quite easily. It is not possible (or desirable) to add build-in functions for everything. And I have no clue is such string function exists in other RDBMS.

if i have gcc and mariadb source here i could create a native function, that`s very fast, but i have only heidisql :) thanks for the idea of replace and difference, that worked very nice =) i don`t know why, but many functions (of php for example) could be implemented easily, but why not implement it? there`s a sql 2000 standard or something like it to disallow this kind of functions? maybe we could create a plugin with many function native to mariadb, and set it on/off to allow a mysql compatibility

-- Peter -- Webyog

On Wed, Mar 19, 2014 at 4:03 PM, Reindl Harald wrote:

...

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

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

2014-03-19 12:40 GMT-03:00 Reindl Harald :

Am 19.03.2014 16:33, schrieb Roberto Spadim:

...

i don`t know why, but many functions (of php for example) could be implemented easily, but why not implement it? there`s a sql 2000 standard or something like it to disallow this kind of functions? maybe we could create a plugin with many function native to mariadb, and set it on/off to allow a mysql compatibility

well, i tell you what happens finally

* most users are not reading any manual * just develop by trial-and-error * starting using all that nice things * then they quit * the person taking their task in future needs to migrate * the application has a "vendor-lockin" and is not portable

and no, i am not speaking for the developers

i am speaking for myself after had to struggle way too often the last 10 years with not well planned software and the resulting upgrade / migration pains

/) i agree with you, i have the same problem many old software with stored functions but for example, well know standards (base64) could be implemented without problems what happened with mysql to only allow base64 at 2013, instead of 2006? i send a patch in 2006 with a udf function and they just looked and forget about that :/ now with json, why not implement it at mariadb? it's just to parse strings i know, but it's very usefull when you have a document inside mariadb (blob column) and need a information inside that column/json text there're many others functions that could be implemented, and i know mariadb have a jira tracker, with priorities, the point is, who tell what is more important and what isn't, and who tell if we could implement or not?

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

wow! thanks kk i was trying to find a php like function your idea is very nice :) thank you! substr_count => (length(field)-length(replace(' ','',field))) /) 2014-03-19 12:16 GMT-03:00 pslawek83 :

Hi Roberto, You'll have to replace char => empty and get the difference in length.

SELECT (length("banana") - replace("n", "", "banana"))

There's probably no "standard" function to do that.

Dnia 19 marca 2014 15:51 Roberto Spadim napisał(a):

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

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

Obivously this is the best solution SELECT LENGTH("banana") - LENGTH( REPLACE("banana","n","")); (so no need or a Stored Function) .. however you may consider to replace LENGTH with CHAR_LENGTH, This is important if you use UTF8 and if you have non-ASCII characters (what you undoubtedly have as a Brazilian person (I guess you are?)). This example with the Spanish "ñ" character illustrates: SET NAMES utf8; SELECT LENGTH("bañaña") - LENGTH( REPLACE("bañaña","ñ","")); -- returns "4" (4 *bytes* more exactly) - and this result is not what your are after! -- whereas SET NAMES utf8; SELECT CHAR_LENGTH("bañaña") - CHAR_LENGTH( REPLACE("bañaña","ñ","")); -- returns "2" (2 *characters* more exactly) - and this is what you want. -- Peter On Wed, Mar 19, 2014 at 6:16 PM, Federico Razzoli wrote:

When a PHP function does something you cannot do in SQL, you could call PHP from your queries using the Gearman UDF.

https://launchpad.net/gearman-mysql-udf

In most cases this should be faster than a stored function. I hope that in the future this will change, but who knows...

Regards, Federico

-------------------------------------------- Mer 19/3/14, Roberto Spadim ha scritto:

Oggetto: Re: [Maria-discuss] help - i'm not finding one sql function A: "pslawek83" Cc: "Maria Discuss" Data: Mercoledì 19 marzo 2014, 16:18

wow! thanks kk i was trying to find a php like functionyour idea is very nice :) thank you! substr_count => (length(field)-length(replace(' ','',field)))

/)

2014-03-19 12:16 GMT-03:00 pslawek83 :

Hi Roberto, You'll have to replace char => empty and get the difference in length.

SELECT (length("banana") - replace("n", "", "banana"))

There's probably no "standard" function to do that.

Dnia 19 marca 2014 15:51 Roberto Spadim napisał(a):

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

--

Roberto Spadim SPAEmpresarial Eng. Automação e Controle

-- Roberto Spadim SPAEmpresarialEng. Automação e Controle

-----Segue allegato-----

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

Carsten, your example is very interesting. I didn't expect that 10 native funcs calls can be 242 times faster (in a real/realistic use case)! A suggestion: one can use macros. strip_digits() can become a macro, so the code will be readable and there won't be performance problems. I use GNU m4 precompiler because I wasn't able to solve a problem with GCC. An example from my macro lib. Definition: m4_define(<>, << SET @`$1` := '$2'; PREPARE `$1` FROM @`$1`; m4_ifelse($3, <<CLOSE>>, <>$1<<` := NULL;>>) EXECUTE `$1`; m4_ifelse($3, <<CLOSE>>, <>$1<<`;>>)

...

)

hi guys, i'm not finding a function to return how many character i have, for example:

"banana"

i want a function

Usage: EXECUTE_SQL(stmt, <>) EXECUTE_SQL(stmt, <>, CLOSE) Regards, Federico -------------------------------------------- Gio 20/3/14, Carsten Pedersen ha scritto: Oggetto: Re: [Maria-discuss] help - i'm not finding one sql function A: "Roberto Spadim" , "Maria Discuss" Data: Giovedì 20 marzo 2014, 09:50 On 19-03-2014 15:51, Roberto Spadim wrote: that return 2 "n" characters, example:

substr_count("banana","n") => 2 SELECT CHAR_LENGTH(field)-CHAR_LENGTH(REPLACE(field, 'n', '')) Many moons ago, I blogged about something similar, performance considerations and how to make your own function: http://www.bitbybit.dk/carsten/blog/?p=55 Best, / Carsten _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to     : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help   : https://help.launchpad.net/ListHelp