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