Category=Sandbox
Name=Various random experiments
Description=split a string into left 5 and rest

Heading=Trial of left and substring
SQL=SELECT cast(left('1004601',5) as signed), right('1004601', length('1004601')-5)

Heading2=Trial of case
SQL2=SELECT case when (1=0) then 0 else concat('0', CAST(1 as char)) end

Heading3=Trial of loop
Import2LSQL3=SELECT '3', '9'|SELECT '{0}', '{1}'|[{0}..{1}]SELECT concat('yoyo', cast({0} as char))

Heading4=expanding math of our verse reference
SQL4=SELECT 2000000000 + (floor(40316/10000)*1000000) + (floor(40316/100%100)*1000) + 40316%100

Import2LSQL5=SELECT '2004003035', '2004004002'|SELECT '{0}', '{1}'|[v{0}..{1}]SELECT concat('yoyo', cast({0} as char))

Heading6=lpad test
SQL6=SELECT DISTINCT '10001', lfNr, folio, cast(lpad(lfNr, 5, '0') as char) from cont10001

Import2LSQL7=SELECT '2004000000', '2004001002'|SELECT '{0}', '{1}'|[v{0}..{1}]SELECT concat('yoyo', cast({0} as char))

Heading8=Roman Numeral Tests [r1:III], [r2:III], [r1:III/IV], [r2:III/IV]
Import2LSQL8=SELECT 'III', 'III/IV'|SELECT '{0}', '{1}'|SELECT [r1:{0}], [r2:{0}], [r1:{1}], [r2:{1}]
SQL9=SELECT right('U-zxssdfsdf', length('U-zxssdfsdf')-2)
ImportSQL10=SELECT BI.REALDOCUMENTID, SI.SHELFID, BI.FOLIOCOUNT*2, SUBSTRING_INDEX(BI.FOLIONUMBER,'-',1) from SHELFINSTANCE SI \
  join BATCHIMPORT BI on SI.INSTITUTIONID = BI.INSTITUTIONID AND BI.SHELFNUMBER = SI.SHELFNUMBER \
where NOT EXISTS (SELECT * FROM SHELFINSTANCEPAGES SIP where SIP.SHELFID = SI.SHELFID) and USERDATA > 0| \
[1..{2}]SELECT		\
		\
{1} DOCUMENTID,	\
COALESCE(MAX(PAGEID), 0) + 10 PAGEID,	\
CASE WHEN CAST('{4}' as UNSIGNED) > 0 then CONCAT(CAST({4}+{0}-1 as char),(CASE WHEN MOD(CAST('{4}' as UNSIGNED),2)=0 THEN 'v' else 'r' END))  else '' END  FOLIONUMBER, \
cast(lpad(COALESCE(MAX(PAGEID), 0) + 10, 5, '0') as char)		\
FROM PAGE WHERE DOCUMENTID={1}| \
[1..{2}]SELECT		\
		\
{1},		\
COALESCE(MAX(PAGEID), 0),	\
{2},		\
CASE WHEN CAST('{4}' as UNSIGNED) > 0 then CONCAT(CAST({4}+{0}-1 as char),(CASE WHEN MOD(CAST('{4}' as UNSIGNED),2)=0 THEN 'v' else 'r' END))  else '' END  FOLIONUMBER,  \
cast(lpad(COALESCE(MAX(PAGEID), 0), 5, '0') as char)		\
FROM PAGE P WHERE DOCUMENTID={1};
