"The left-most character of X is number 1. If Y is negative then the first character of the substring is found by counting from the right rather than the left."
The substr(x,y,z) function is defined only for nonzero values of y. SQlite can return whatever it feels like if you insist on providing invalid input. With "being nice to the user" and "making a best effort to return sensible data even for nonsense input" as design goals, mapping substr(x,0,z) to substr(x,1,z-1) seems quite a benign solution. -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jean-Luc Hainaut Gesendet: Montag, 26. Februar 2018 10:53 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] Re: [sqlite] Strange concatenation result About the "substr(X,Y,Z)" function, I observe a strange behaviour when Y = 0. If I execute this script: select 'abcd',substr('abcd',0,1),substr('abcd',1,1),substr('abcd',2,1); select 'abcd',substr('abcd',0,2),substr('abcd',1,2),substr('abcd',2,2); select 'abcd',substr('abcd',0,9),substr('abcd',1,9),substr('abcd',2,9); select 'abcd',substr('abcd',0),substr('abcd',1),substr('abcd',2); It prints: +--------+--------------------+--------------------+--------------------+ | 'abcd' | substr('abcd',0,1) | substr('abcd',1,1) | substr('abcd',2,1) | | +--------+--------------------+--------------------+--------------------+ | abcd | | a | b | +--------+--------------------+--------------------+--------------------+ +--------+--------------------+--------------------+--------------------+ | 'abcd' | substr('abcd',0,2) | substr('abcd',1,2) | substr('abcd',2,2) | | +--------+--------------------+--------------------+--------------------+ | abcd | a | ab | bc | +--------+--------------------+--------------------+--------------------+ +--------+--------------------+--------------------+--------------------+ | 'abcd' | substr('abcd',0,9) | substr('abcd',1,9) | substr('abcd',2,9) | | +--------+--------------------+--------------------+--------------------+ | abcd | abcd | abcd | bcd | +--------+--------------------+--------------------+--------------------+ +--------+------------------+------------------+------------------+ | 'abcd' | substr('abcd',0) | substr('abcd',1) | substr('abcd',2) | +--------+------------------+------------------+------------------+ | abcd | abcd | abcd | bcd | +--------+------------------+------------------+------------------+ It seems that Y=0 denotes a fictitious empty position before the first one (Y=1).Is it the intended behaviour? The documentation (https://www.sqlite.org/lang_corefunc.html#substr), says nothing about this specific pattern. J-L Hainaut _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
