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

-----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 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 -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQEcBAEBCAAGBQJWMy0CAAoJEISMxT6LrWYgh58IAK/00HESTY5y5cEy0uV1eksa 6XyXv656olvfgG2cnFE5KXiLWcNKUKSqB6Pq6F/qqaTCeY6XTW3uGGJCpDsP3xUq y9V2BVPn5bjuexu61Xce33t7Fq88yn+Qsg1IKV9OzUtk/WhAfTT9fyf5K80VaRT5 1VGKZyN8RzwPdOMSV9t5OBWKdmPUah19DQ7cMbq0iUBi7eDXQAUiyfX/wYUr425E Tl+xujQ/NG+fBdzg93CcVAFyVu1j/4gcIkKx9c7UyQnK64l7Csc1ZcLqvDjhfZ2g +b7EjHjsyh+FfaTcyhIYDz6rHuNWSxhDsHPFM3sk8Zj350jJpPp/i19zOza3rrg= =9unI -----END PGP SIGNATURE-----

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

-- Parc d'activité Tolstoï 4 rue Edouard Aynard 69100 Villeurbanne Tél. +33 (0)4 78 95 05 80 Fax +33 (0)4 78 95 00 17 www.passman.fr www.passman-hotels.com www.passman-camping.com www.passman-sante.com https://www.facebook.com/PASSMAN-187787814053/