[Maria-discuss] help - i'm not finding one sql function
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
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
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@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
_______________________________________________ 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
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@thelounge.net <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
@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@thelounge.net>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@thelounge.net<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
_______________________________________________ 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
/) 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 <peter_laursen@webyog.com>:
@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@thelounge.net>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@thelounge.net<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
_______________________________________________ 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 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@webyog.com> ha scritto: Oggetto: Re: [Maria-discuss] help - i'm not finding one sql function A: "Reindl Harald" <h.reindl@thelounge.net> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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@thelounge.net> 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@thelounge.net <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 _______________________________________________ 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 -----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
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 <h.reindl@thelounge.net>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 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 Am 19.03.2014 16:25, schrieb 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!"
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 <h.reindl@thelounge.net <mailto:h.reindl@thelounge.net>> 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
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 <h.reindl@thelounge.net>:
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 <h.reindl@thelounge.net<mailto: h.reindl@thelounge.net>> 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
here for example https://github.com/search?q=mysql+udf&type=Repositories&ref=searchresults i can find many usefull functions =) but, why not include some of these functions inside mariadb?
2014-03-19 12:25 GMT-03:00 Peter Laursen <peter_laursen@webyog.com>:
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 <h.reindl@thelounge.net>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
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
2014-03-19 12:40 GMT-03:00 Reindl Harald <h.reindl@thelounge.net>:
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
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 <roberto@spadim.com.br> 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 SPAEmpresarialEng. 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 <pslawek83@o2.pl>:
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 <roberto@spadim.com.br> 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
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 <roberto@spadim.com.br> ha scritto: Oggetto: Re: [Maria-discuss] help - i'm not finding one sql function A: "pslawek83" <pslawek83@o2.pl> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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 <pslawek83@o2.pl>: 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 <roberto@spadim.com.br> 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
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 <federico_raz@yahoo.it>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 <roberto@spadim.com.br> ha scritto:
Oggetto: Re: [Maria-discuss] help - i'm not finding one sql function A: "pslawek83" <pslawek83@o2.pl> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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 <pslawek83@o2.pl>:
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 <roberto@spadim.com.br> 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
Hi Peter! 2014-03-19 18:03 GMT-03:00 Peter Laursen <peter_laursen@webyog.com>:
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:
Yes =D Brazil heeh, sorry my english, i change some words and wrote sometimes as i think in portuguese (are you going? => you are going? kk portuguese have a different grammar) No problem :) i'm using latin1 here at columns and server and client
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.
yeap :) i know how to use charsets, char_length is the right function with multibyte text, but no problem :) i considred a nice idea form adam instead of remove the character, add a new character length(replace('banana','n','n+'))-length('banana') if i replace 'asdfasdf' with 'asdfasdf+', i have only one new character :) that's nice
-- Peter
thanks!
On Wed, Mar 19, 2014 at 6:16 PM, Federico Razzoli <federico_raz@yahoo.it>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 <roberto@spadim.com.br> ha scritto:
Oggetto: Re: [Maria-discuss] help - i'm not finding one sql function A: "pslawek83" <pslawek83@o2.pl> Cc: "Maria Discuss" <maria-discuss@lists.launchpad.net> 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 <pslawek83@o2.pl>:
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 <roberto@spadim.com.br> 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
_______________________________________________ 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
try this: SELECT LENGTH("banana") - LENGTH( REPLACE("banana","n","")); or this (starting from mariadb 10): SELECT LENGTH(REGEXP_REPLACE('banana','[^n]','')) ; Jan 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
-- Roberto Spadim SPAEmpresarial Eng. Automação e Controle
_______________________________________________ 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
On 19-03-2014 15:51, Roberto Spadim wrote:
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
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
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(<<EXECUTE_SQL>>, << SET @`$1` := '$2'; PREPARE `$1` FROM @`$1`; m4_ifelse($3, <<CLOSE>>, <<SET @`>>$1<<` := NULL;>>) EXECUTE `$1`; m4_ifelse($3, <<CLOSE>>, <<DEALLOCATE PREPARE `>>$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, <<SELECT 1;>>) EXECUTE_SQL(stmt, <<SELECT 2;>>, CLOSE) Regards, Federico -------------------------------------------- Gio 20/3/14, Carsten Pedersen <carsten@bitbybit.dk> ha scritto: Oggetto: Re: [Maria-discuss] help - i'm not finding one sql function A: "Roberto Spadim" <roberto@spadim.com.br>, "Maria Discuss" <maria-discuss@lists.launchpad.net> 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
participants (7)
-
Carsten Pedersen -
Federico Razzoli -
Jan Kirchhoff -
Peter Laursen -
pslawek83 -
Reindl Harald -
Roberto Spadim