String functions support in MonetDB
Dear all,
Is there a way to concatenate more than 2 strings at once? The CONCAT function only allow for concatenating 2 strings.
Also, hhe JDBC driver for MonetDB reports that the following string functions are supported (see the list below). Are these functions documented somewhere?
ascii char_length character_length code concat copyfrom difference editdistance editdistance2 get_value_for ilike index insert lcase left length levenshtein like locate lower lpad ltrim next_value_for not_ilike not_like octet_length patindex qgramnormalize repeat replace restart right rpad rtrim similarity soundex space splitpart strings substring trim truncate ucase upper
Best regards, Sebastien
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 29/10/15 10:46, Sébastien RAILLARD (PASSMAN) wrote:
Dear all,
Is there a way to concatenate more than 2 strings at once? The CONCAT function only allow for concatenating 2 strings.
You can use the || operator to concatenate strings, and then concatenating more than 2 is easy:
'one' || 'two' || 'three'
Also, hhe JDBC driver for MonetDB reports that the following string functions are supported (see the list below). Are these functions documented somewhere?
ascii char_length character_length code concat copyfrom difference editdistance editdistance2 get_value_for ilike index insert lcase left length levenshtein like locate lower lpad ltrim next_value_for not_ilike not_like octet_length patindex qgramnormalize repeat replace restart right rpad rtrim similarity soundex space splitpart strings substring trim truncate ucase upper
Best regards, Sebastien
- -- Sjoerd Mullender
On 29/10/15 10:46, Sébastien RAILLARD (PASSMAN) wrote:
Dear all,
Is there a way to concatenate more than 2 strings at once? The CONCAT function only allow for concatenating 2 strings.
You can use the || operator to concatenate strings, and then concatenating more than 2 is easy:
'one' || 'two' || 'three'
Thanks for the || tip, it's excatly what I was searching for!
Also, hhe JDBC driver for MonetDB reports that the following string functions are supported (see the list below). Are these functions documented somewhere?
ascii char_length character_length code concat copyfrom difference editdistance editdistance2 get_value_for ilike index insert lcase left length levenshtein like locate lower lpad ltrim next_value_for not_ilike not_like octet_length patindex qgramnormalize repeat replace restart right rpad rtrim similarity soundex space splitpart strings substring trim truncate ucase upper
Best regards, Sebastien
Hi Sjoerd,
While using the concat function and || operator, if any of the operands are null, then the result becomes null.
For example, if first name is null and last name has some value string, when I concat these two to get the full name, the full name comes as null.
What if null would be treated as an empty character and on concatenation it would give the available operand as result?
P.S. : I agree that we could store the empty character instead of null to get the desired result. But instead of the concat operator showing null for actual values, it could show the least available value.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305.
On Thu, Oct 29, 2015 at 5:59 PM, Sjoerd Mullender sjoerd@acm.org wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 29/10/15 10:46, Sébastien RAILLARD (PASSMAN) wrote:
Dear all,
Is there a way to concatenate more than 2 strings at once? The CONCAT function only allow for concatenating 2 strings.
You can use the || operator to concatenate strings, and then concatenating more than 2 is easy:
'one' || 'two' || 'three'
Also, hhe JDBC driver for MonetDB reports that the following string functions are supported (see the list below). Are these functions documented somewhere?
ascii char_length character_length code concat copyfrom difference editdistance editdistance2 get_value_for ilike index insert lcase left length levenshtein like locate lower lpad ltrim next_value_for not_ilike not_like octet_length patindex qgramnormalize repeat replace restart right rpad rtrim similarity soundex space splitpart strings substring trim truncate ucase upper
Best regards, Sebastien
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWMhEWAAoJEISMxT6LrWYgeV4H/RvyBMbA2Bv7EvVr37fTDESh ASoijS5vAwXsAk3TPfzQ8wLYPdUNee6a7I9qaOnSI/D7FEzo0DtkLDwv/Hpqxs5z FWplqqkD/JAGPdASQrcbPdT1Gx+3ytXxpvJ5f30NkyaTGsW/OF/yHb8p6tXk74nw cBEA/AWbPpKqw8AloUT7PcTh3fgfp/2QoRAU2dEaxoB55vyuS26QXLmrhAITcvSU iXkaWUtyssV6pf/v2F+ePEMUAcn8xkFWte4p5G7FejRbGjdC9c+WLrupcgskZgU5 ilaatbNy4t1OyB2JSKmOVUUZeUbX4zCvl6C5W0m+Ayl5lUa0cujDRRpoF2S7TTM= =lbql -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
hi vijay, i believe the current treatment of nulls is consistent with other sql flavors. here is what sqlite does through R
sqldf("SELECT NULL||'hi'")
NULL||'hi' 1 <NA>
i think you can modify the behavior with case statements or a custom function, depending on your use case..
On Fri, Oct 30, 2015 at 1:42 AM, Vijay Krishna vijayakrishna55@gmail.com wrote:
Hi Sjoerd,
While using the concat function and || operator, if any of the operands are null, then the result becomes null.
For example, if first name is null and last name has some value string, when I concat these two to get the full name, the full name comes as null.
What if null would be treated as an empty character and on concatenation it would give the available operand as result?
P.S. : I agree that we could store the empty character instead of null to get the desired result. But instead of the concat operator showing null for actual values, it could show the least available value.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305.
On Thu, Oct 29, 2015 at 5:59 PM, Sjoerd Mullender sjoerd@acm.org wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 29/10/15 10:46, Sébastien RAILLARD (PASSMAN) wrote:
Dear all,
Is there a way to concatenate more than 2 strings at once? The CONCAT function only allow for concatenating 2 strings.
You can use the || operator to concatenate strings, and then concatenating more than 2 is easy:
'one' || 'two' || 'three'
Also, hhe JDBC driver for MonetDB reports that the following string functions are supported (see the list below). Are these functions documented somewhere?
ascii char_length character_length code concat copyfrom difference editdistance editdistance2 get_value_for ilike index insert lcase left length levenshtein like locate lower lpad ltrim next_value_for not_ilike not_like octet_length patindex qgramnormalize repeat replace restart right rpad rtrim similarity soundex space splitpart strings substring trim truncate ucase upper
Best regards, Sebastien
Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWMhEWAAoJEISMxT6LrWYgeV4H/RvyBMbA2Bv7EvVr37fTDESh ASoijS5vAwXsAk3TPfzQ8wLYPdUNee6a7I9qaOnSI/D7FEzo0DtkLDwv/Hpqxs5z FWplqqkD/JAGPdASQrcbPdT1Gx+3ytXxpvJ5f30NkyaTGsW/OF/yHb8p6tXk74nw cBEA/AWbPpKqw8AloUT7PcTh3fgfp/2QoRAU2dEaxoB55vyuS26QXLmrhAITcvSU iXkaWUtyssV6pf/v2F+ePEMUAcn8xkFWte4p5G7FejRbGjdC9c+WLrupcgskZgU5 ilaatbNy4t1OyB2JSKmOVUUZeUbX4zCvl6C5W0m+Ayl5lUa0cujDRRpoF2S7TTM= =lbql -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
You can use the coalesce function:
coalesce(a, '') || coalesce(b, '')
coalesce returns the first argument if it is not null, and returns the second if the first is null.
On 30/10/15 06:42, Vijay Krishna wrote:
Hi Sjoerd,
While using the concat function and || operator, if any of the operands are null, then the result becomes null.
For example, if first name is null and last name has some value string, when I concat these two to get the full name, the full name comes as null.
What if null would be treated as an empty character and on concatenation it would give the available operand as result?
P.S. : I agree that we could store the empty character instead of null to get the desired result. But instead of the concat operator showing null for actual values, it could show the least available value.
Thanks & Regards,
Vijayakrishna.P. Mobile : (+91) 9500402305.
On Thu, Oct 29, 2015 at 5:59 PM, Sjoerd Mullender <sjoerd@acm.org mailto:sjoerd@acm.org> wrote:
On 29/10/15 10:46, Sébastien RAILLARD (PASSMAN) wrote:
Dear all,
Is there a way to concatenate more than 2 strings at once? The CONCAT function only allow for concatenating 2 strings.
You can use the || operator to concatenate strings, and then concatenating more than 2 is easy:
'one' || 'two' || 'three'
Also, hhe JDBC driver for MonetDB reports that the following string functions are supported (see the list below). Are these functions documented somewhere?
ascii char_length character_length code concat copyfrom difference editdistance editdistance2 get_value_for ilike index insert lcase left length levenshtein like locate lower lpad ltrim next_value_for not_ilike not_like octet_length patindex qgramnormalize repeat replace restart right rpad rtrim similarity soundex space splitpart strings substring trim truncate ucase upper
Best regards, Sebastien
_______________________________________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
- -- Sjoerd Mullender
Hi Sébastien,
Are these functions documented somewhere?
There is currently no MonetDB documentation on the built-in SQL scalar functions. It is on our ToDo-list. However most SQL scalar functions conform to standard SQL and behave similar to other RDBMS.
Alternatively you can query following sys tables to find out which functions and arguments exist: select * from sys.functions; select * from sys.args;
See also: https://www.monetdb.org/Documentation/SQLcatalog/Functions%2C%20arguments%2C...
Regards, Martin van Dinther
On 29-10-15 10:46, Sébastien RAILLARD (PASSMAN) wrote:
Dear all,
Is there a way to concatenate more than 2 strings at once? The CONCAT function only allow for concatenating 2 strings.
Also, hhe JDBC driver for MonetDB reports that the following string functions are supported (see the list below). Are these functions documented somewhere?
ascii char_length character_length code concat copyfrom difference editdistance editdistance2 get_value_for ilike index insert lcase left length levenshtein like locate lower lpad ltrim next_value_for not_ilike not_like octet_length patindex qgramnormalize repeat replace restart right rpad rtrim similarity soundex space splitpart strings substring trim truncate ucase upper
Best regards, Sebastien
Thank you for the information.
-----Original Message----- From: martin van dinther [mailto:martin.van.dinther@monetdbsolutions.com] Sent: jeudi 19 novembre 2015 17:45 To: Communication channel for MonetDB users; sebastien.raillard@passman.fr Subject: Re: String functions support in MonetDB
Hi Sébastien,
Are these functions documented somewhere?
There is currently no MonetDB documentation on the built-in SQL scalar functions. It is on our ToDo-list. However most SQL scalar functions conform to standard SQL and behave similar to other RDBMS.
Alternatively you can query following sys tables to find out which functions and arguments exist: select * from sys.functions; select * from sys.args;
See also: https://www.monetdb.org/Documentation/SQLcatalog/Functions%2C%20argument s%2C%20types
Regards, Martin van Dinther
On 29-10-15 10:46, Sébastien RAILLARD (PASSMAN) wrote:
Dear all,
Is there a way to concatenate more than 2 strings at once? The CONCAT function only allow for concatenating 2 strings.
Also, hhe JDBC driver for MonetDB reports that the following string functions are supported (see the list below). Are these functions documented somewhere?
ascii char_length character_length code concat copyfrom difference editdistance editdistance2 get_value_for ilike index insert lcase left length levenshtein like locate lower lpad ltrim next_value_for not_ilike not_like octet_length patindex qgramnormalize repeat replace restart right rpad rtrim similarity soundex space splitpart strings substring trim truncate ucase upper
Best regards, Sebastien
participants (5)
-
Anthony Damico -
martin van dinther -
Sjoerd Mullender -
Sébastien RAILLARD (PASSMAN) -
Vijay Krishna