1# 2# SUBSTR() function 3# Author: Bharath, bharath.bokka@mariadb.com 4# 5-- source ../include/have_columnstore.inc 6 7--disable_warnings 8DROP DATABASE IF EXISTS mcs273_db; 9--enable_warnings 10 11CREATE DATABASE mcs273_db; 12USE mcs273_db; 13 14CREATE TABLE t1 15( 16 t1_INT INT, 17 t1_DECIMAL DECIMAL(12,5), 18 t1_TEXT TEXT, 19 t1_DATE DATE, 20 t1_TIME TIME 21 )ENGINE=Columnstore; 22INSERT INTO t1 VALUES(103, 1234.5699, 'pqr stu vwx yz', '1997-12-12', '22:12:02'); 23INSERT INTO t1 VALUES(-7299, 111.99, 'abcde//fghijk//lm', '2001-1-1', '23:59:59'); 24INSERT INTO t1 VALUES(9913, 98765.4321, repeat('q00', 5), '09-12-11', '01:08:59'); 25 26SELECT SUBSTR('123456789', 5, 3) FROM t1 LIMIT 1; 27SELECT SUBSTR('abcdefghij', 3, 5) FROM t1 LIMIT 1; 28SELECT SUBSTR('pqr stu vwz', 3, 5) FROM t1 LIMIT 1; 29SELECT SUBSTR('pqr stu vwz', 3) FROM t1 LIMIT 1; 30SELECT SUBSTR('pqr stu vwz' FROM 3) FROM t1 LIMIT 1; 31SELECT SUBSTR('11:12:13' FROM 4 FOR 2) FROM t1 LIMIT 1; 32SELECT SUBSTR('11:12:13', 4, 2) FROM t1 LIMIT 1; 33 34SELECT SUBSTR(t1_TEXT FROM 3) FROM t1 ORDER BY 1; 35SELECT SUBSTR(t1_TEXT, 3) FROM t1 ORDER BY 1; 36SELECT SUBSTR(t1_TEXT FROM 3 FOR 15) FROM t1 ORDER BY 1; 37SELECT SUBSTR(t1_TEXT, 3, 15) FROM t1 ORDER BY 1; 38 39SELECT SUBSTR(t1_DATE FROM 3) FROM t1 ORDER BY 1; 40SELECT SUBSTR(t1_DATE, 3) FROM t1 ORDER BY 1; 41SELECT SUBSTR(t1_DATE FROM 3 FOR 15) FROM t1 ORDER BY 1; 42SELECT SUBSTR(t1_DATE, 3, 15) FROM t1 ORDER BY 1; 43 44SELECT SUBSTR(t1_TIME FROM 3) FROM t1 ORDER BY 1; 45SELECT SUBSTR(t1_TIME, 3) FROM t1 ORDER BY 1; 46SELECT SUBSTR(t1_TIME FROM 3 FOR 15) FROM t1 ORDER BY 1; 47SELECT SUBSTR(t1_TIME, 3, 15) FROM t1 ORDER BY 1; 48 49# Clean UP 50DROP DATABASE mcs273_db; 51