sql-tests/inputs/string-functions.sql

0001 -- Argument number exception
0002 select concat_ws();
0003 select format_string();
0004
0005 -- A pipe operator for string concatenation
0006 select 'a' || 'b' || 'c';
0007
0008 -- replace function
0009 select replace('abc', 'b', '123');
0010 select replace('abc', 'b');
0011
0012 -- uuid
0013 select length(uuid()), (uuid() <> uuid());
0014
0015 -- position
0016 select position('bar' in 'foobarbar'), position(null, 'foobarbar'), position('aaads', null);
0017
0018 -- left && right
0019 select left("abcd", 2), left("abcd", 5), left("abcd", '2'), left("abcd", null);
0020 select left(null, -2), left("abcd", -2), left("abcd", 0), left("abcd", 'a');
0021 select right("abcd", 2), right("abcd", 5), right("abcd", '2'), right("abcd", null);
0022 select right(null, -2), right("abcd", -2), right("abcd", 0), right("abcd", 'a');
0023
0024 -- split function
0025 SELECT split('aa1cc2ee3', '[1-9]+');
0026 SELECT split('aa1cc2ee3', '[1-9]+', 2);
0027
0028 -- substring function
0029 SELECT substr('Spark SQL', 5);
0030 SELECT substr('Spark SQL', -3);
0031 SELECT substr('Spark SQL', 5, 1);
0032 SELECT substr('Spark SQL' from 5);
0033 SELECT substr('Spark SQL' from -3);
0034 SELECT substr('Spark SQL' from 5 for 1);
0035 SELECT substring('Spark SQL', 5);
0036 SELECT substring('Spark SQL', -3);
0037 SELECT substring('Spark SQL', 5, 1);
0038 SELECT substring('Spark SQL' from 5);
0039 SELECT substring('Spark SQL' from -3);
0040 SELECT substring('Spark SQL' from 5 for 1);
0041
0042 -- trim
0043 SELECT trim(" xyz "), ltrim(" xyz "), rtrim(" xyz ");
0044 SELECT trim(BOTH 'xyz' FROM 'yxTomxx'), trim('xyz' FROM 'yxTomxx');
0045 SELECT trim(BOTH 'x' FROM 'xxxbarxxx'), trim('x' FROM 'xxxbarxxx');
0046 SELECT trim(LEADING 'xyz' FROM 'zzzytest');
0047 SELECT trim(LEADING 'xyz' FROM 'zzzytestxyz');
0048 SELECT trim(LEADING 'xy' FROM 'xyxXxyLAST WORD');
0049 SELECT trim(TRAILING 'xyz' FROM 'testxxzx');
0050 SELECT trim(TRAILING 'xyz' FROM 'xyztestxxzx');
0051 SELECT trim(TRAILING 'xy' FROM 'TURNERyxXxy');
0052
0053 -- Check lpad/rpad with invalid length parameter
0054 SELECT lpad('hi', 'invalid_length');
0055 SELECT rpad('hi', 'invalid_length');