SQLite format 3@ > viewDecoderDecoderCREATE VIEW Decoder AS Select * , nullif(substr(RMcode,2,1),'.') AS C2, nullif(substr(RMcode,13,1),'.') AS C13, ifnull( substr(nullif(substr(RMcode,1,1),'D')||RMcode,3), substr(nullif(substr(RMcode,1,1),'T') || COALESCE( nullif(REPLACE(substr(RMcode,2,1),'Y','by '),substr(RMcode,2,1)), nullif(REPLACE(substr(RMcode,2,1),'B','bef '),substr(RMcode,2,1)), nullif(REPLACE(substr(RMcode,2,1),'T','to '),subs}''ctableTemp_64549854Temp_64549854CREATE TABLE [Temp_64549854]( [Display] text(50) COLLATE NOCASE ,[RMcode] text(24) COLLATE nOCASE [tablemodsmodsCREATE TABLE "mods"( [Modifier] TEXT ,[C2] text ,[C13] text )WtableFormatsFormatsCREATE TABLE "Formats"( [Display] text(50) COLLATE NOCASE ,[RMcode] text(24) COLLATE nOCASE  ?pF&m@b6*+=Cert 1 Jan 1900D.+19000101.6+00000000..*+=Prob 1 Jan 1900D.+19000101.5+00000000..*+=Poss 1 Jan 1900D.+19000101.4+00000000..*+=Lkly 1 Jan 1900D.+19000101.3+00000000..+-=Appar 1 Jan 1900D.+19000101.2+00000000..+-=Prhps 1 Jan 1900D.+19000101.1+00000000..+-=Maybe 1 Jan 1900D.+19000101.?+00000000..#=Jan 1900D.+19000100..+00000000..%!=1 ??? 1900D.+19000001..+00000000..=1900D.+19000000..+00000000..( '=1 Jan 1583/84D.+15830101/.+00000000.. =1 JanD.+00000101..+00000000.. =JanD.+00000100..+00000000..( '=1 Jan 1900 BCD.-19000101..+00000000..2 ;=1 Jan 1900–5 Jan 1900D-+19000101..+19000105..NULL."=No DateD.+00000000..+00000000..&/1This is free textTThis is free textC]=From maybe 1 Apr 54BC to maybe 31 Dec 3 DS-00540401.?-00031231.?6C=Bet 1 Jan 1900 and Jan 1901DR+19000101..+19010100..5A=1 Jan 1900 and 5 Jan 1900 DR+19000101..+19000105/.%!=1899/1900 D.+18990000/.+00000000..))=1 Jan 1949/50 D.+19490101/.+00000  D{5==-1 Jan 1900–5 Jan 1900 D-+19000101..+19000105..,#=calc 1 Jan 1900 D.+19000101.L+00000000..+#=est 1 Jan 1900 D.+19000101.E+00000000..+#=abt 1 Jan 1900 D.+19000101.A+00000000..7?=or1 Jan 1900 or 5 Jan 1900 DO+19000101..+19000105..:?=from 1 Jan 1900 to 5 Jan 1900 DS+19000101..+19000105..:A=bet 1 Jan 1900 and 5 Jan 1900 DR+19000101..+19000105/.+#=aft 1 Jan 1900 DA+19000101..+00000000..- #=since 1 Jan 1900 DI+19000101..+00000000.., #=from 1 Jan 1900 DF+19000101..+00000000..- #=until 1 Jan 1900 DU+19000101..+00000000..* #=to 1 Jan 1900 DT+19000101..+00000000..* #=by 1 Jan 1900 DY+19000101..+00000000..+#=bef 1 Jan 1900 DB+19000101..+00000000..'#=1 ??? 1900 D.+19000001..+00000000.. =Jan D.+00000100..+00000000.."=1 Jan D.+00000101..+00000000..!=1900 D.+18990000/.+00000000..%=Jan 1900 D.+19000100..+00000000..*)=1 Jan 1900 BC D.-19000101..+00000000..'#=1 Jan 1900 D.+19490101/.+00000000.. MHh!N{66*+=Cert 1 Jan 1900D.+19000101.6+00000000..*+=Prob 1 Jan 1900D.+19000101.5+00000000))=1 Jan 1949/50 D.+19490101/.+00000000..%!=1899/1900 D.+18990000/.+00000000..5A=1 Jan 1900 and 5 Jan 1900 DR+19000101..+19000105/.6C=Bet 1 Jan 1900 and Jan 1901DR+19000101..+19010100..Ea=From maybe 1 Apr 54BC to maybe 31 Dec 3BC DS-00540401.?-00031231.?&/1This is free textTThis is free text"=No DateD.+00000000..+00000000..NULL.2 ;=1 Jan 1900–5 Jan 1900D-+19000101..+19000105..( '=1 Jan 1900 BCD.-19000101..+00000000.. =JanD.+00000100..+00000000.. =1 JanD.+00000101..+00000000..( '=1 Jan 1583/84D.+15830101/.+00000000..=1900D.+19000000..+00000000..%!=1 ??? 1900D.+19000001..+00000000..#=Jan 1900D.+19000100..+00000000..+-=Maybe 1 Jan 1900D.+19000101.?+00000000..+-=Prhps 1 Jan 1900D.+19000101.1+00000000..+-=Appar 1 Jan 1900D.+19000101.2+00000000..*+=Lkly 1 Jan 1900D.+19000101.3+00000000..*+=Poss 1 Jan 1900D.+19000101.4+00000000.. 12 , 1 ) AS C12 , Substr( Rmcode , 13 , 1 ) AS C13 , Substr( Rmcode , 14 , 1 ) AS C14 , Substr( Rmcode , 23 , 1 ) AS C23 , Substr( Rmcode , 24 , 1 ) AS C24 , CASE Substr( Rmcode , 1 , 1 ) WHEN 'Q' THEN Rmcode WHEN 'T' THEN Substr( Rmcode , 2 ) WHEN 'D' THEN CASE Substr( Rmcode , 2 , 1 ) WHEN 'A' THEN 'aft ' WHEN 'B' THEN 'bef ' WHEN 'F' THEN 'from ' WHEN 'I' THEN 'since ' WHEN 'R' THEN 'bet ' WHEN 'S' THEN 'from ' WHEN 'T' THEN 'to ' WHEN 'U' THEN 'until ' WHEN 'Y' THEN 'by ' ELSE '' END || CASE Substr( Rmcode , 13 , 1 ) WHEN 'A' THEN 'abt ' WHEN 'C' THEN 'ca ' WHEN 'E' THEN 'est ' WHEN 'L' THEN 'calc ' WHEN 'S' THEN 'say ' WHEN '6' THEN 'cert ' WHEN '5' THEN 'prob ' WHEN '4' THEN 'poss ' WHEN '3' THEN 'lkly ' WHEN '2' THEN 'appar ' WHEN '1' THEN 'prhps ' WHEN '?' THEN 'maybe ' ELSE '' END || CASE WHEN Substr( Rmcode , 3 , 1 ) = '-' THEN 'BC' ELSE ' }rf\QE9-! maybe ? prhps 1 appar 2 lkly 3 poss 4 prob 5 cert 6 say Sca C calc L est E abt A orO -- from S bet R aft A since I from F until Uto Tby Y bef B' END || CASE WHEN Substr( Rmcode , 4 , 4 ) = '0000' THEN '' ELSE Substr( Rmcode , 4 , 4 ) END || CASE WHEN Substr( Rmcode , 12 , 1 ) = '/' THEN '/' || ( 1 + Substr( Rmcode , 3 , 5 ) ) ELSE '' END || CASE WHEN Substr( Rmcode , 8 , 2 ) = '00' AND ( Substr( Rmcode , 4 , 4 ) <> '0000' AND Substr( Rmcode , 10 , 2 ) <> '00' ) THEN '-??' WHEN Substr( Rmcode , 8 , 2 ) = '00' AND ( Substr( Rmcode , 4 , 4 ) <> '0000' AND Substr( Rmcode , 10 , 2 ) == '00' ) THEN '' WHEN Substr( Rmcode , 8 , 2 ) = '00' THEN '' ELSE '-' || Substr( Rmcode , 8 , 2 ) END || Coalesce( Nullif( '-' || Substr( Rmcode , 10 , 2 ) , '-00' ) , '' ) || CASE Substr( Rmcode , 2 , 1 ) WHEN 'R' THEN ' and ' WHEN 'S' THEN ' to ' WHEN 'O' THEN ' or ' WHEN '-' THEN ' - ' ELSE '' END || CASE Substr( Rmcode , 24 , 1 ) WHEN 'A' THEN 'abt ' WHEN 'E' THEN 'est ' WHEN 'L' THEN 'calc ' WHEN 'C' THEN 'ca ' WHEN 'S' THEN 'say ' WHEN '6' THEN 'cert ' WHEN '5' THEN 'prob ' WHEN '4' THEN 'poss ' WHEN '3' THEN 'lkly ' WHEN '2' THEN 'appar ' WHEN '1' THEN 'prhps ' WHEN '?' THEN 'maybe ' ELSE '' END || CASE WHEN Substr( Rmcode , 14 , 1 ) = '-' THEN 'BC' ELSE '' END || CASE WHEN Substr( Rmcode , 15 , 4 ) = '0000' THEN '' ELSE Substr( Rmcode , 15 , 4 ) END || CASE WHEN Substr( Rmcode , 23 , 1 ) = '/' THEN '/' || ( 1 + Substr( Rmcode , 14 , 5 ) ) ELSE '' END || CASE WHEN Substr( Rmcode , 19 , 2 ) = '00' AND ( Substr( Rmcode , 15 , 4 ) <> '0000' AND Substr( Rmcode , 21 , 2 ) <> '00' ) THEN '-??' WHEN Substr( Rmcode , 19 , 2 ) = '00' AND ( Substr( Rmcode , 15 , 4 ) <> '0000' AND Substr( Rmcode , 21 , 2 ) == '00' ) THEN '' WHEN Substr( Rmcode , 19 , 2 ) = '00' THEN '' ELSE '-' || Substr( Rmcode , 19 , 2 ) END || Coalesce( Nullif( '-' || Substr( Rmcode , 21 , 2 ) , '-00' ) , '' ) ELSE '' END AS Decoded FROM Main.[formats] ORDER BY RMcode   }[tablemodsmodsCREATE TABLE "mods"( [Modifier] TEXT ,[C2] text ,[C13] text )WtableFormatsFormatsCREATE TABLE "Formats"( [Display] text(50) COLLATE NOCASE ,[RMcode] text(24) COLLATE nOCASE )  B!Y1 t? iA&#=1 Jan 1900 D.+19000101.S+00000000..&#=1 Jan 1900 D.+19000101.C+00000000..&#=1 Jan 1900 D.+19000101.L+00000000..&#=1 Jan 1()viewDecoderDecoderCREATE VIEW [Decoder] As -- DateDecoder.sql -- Decodes RM4 Dates with 'D' and 'T' prefixes. -- #1 2010-01-20 ve3meo -- uses a sample database RMDateFormats.db3 with the table Formats -- no Quaker, double year; constructed using COALESCE -- #2 2010-01-20 rev by ve3meo: discovered CASE and replaced most COALESCE. -- Much cleaner; double years still no Quaker, nor months in abbrev. -- Awfully big to have to plug in for every date type field; argues for -- an extended function in sqlite or a similar function in the calling program. -- #3 2010-01-21 rev by ve3meo: Quaker dates now brought out undecoded rather than null. -- more modifier code slots displayed. SELECT * , Substr( Rmcode , 2 , 1 ) AS C2 , substr( Rmcode , 3 , 1 ) AS C3 , Substr( Rmcode , : 6a:g:g:M(q=the first Wednesday after the big fire in Chicago.Tthe first Wednesday aft*+=Prob 1 Jan 1900D.+19000101.5+00000000..*+=Cert 1 Jan 1900D.+19000101.6+00000000..))=Abt 1 Jan 1900D.+19000101.A+00000000..('=Ca 1 Jan 1900D.+19000101.C+00000000..))=Est 1 Jan 1900D.+19000101.E+00000000..*+=Calc 1 Jan 1900D.+19000101.L+00000000..))=Say 1 Jan 1900D.+19000101.S+00000000..))=Aft 1 Jan 1900DA+19000101..+00000000..))=Bef 1 Jan 1900DB+19000101..+00000000..*+=From 1 Jan 1900DF+19000101..+00000000..+ -=Since 1 Jan 1900DI+19000101..+00000000..3!==1 Jan 1900 or 5 Jan 1900DO+19000101..+19000105..8"G=Bet 1 Jan 1900 and 5 Jan 1900DR+19000101..+19000105..8#G=From 1 Jan 1900 to 5 Jan 1900DS+19000101..+19000105..($'=To 1 Jan 1900DT+19000101..+00000000..+%-=Until 1 Jan 1900DU+19000101..+00000000..(&'=By 1 Jan 1900DY+19000101..+00000000..(''=12da 5mo 1588Q.+15880512..+00000000..E=the first Wednesday after the big fire iTthe first Wednesday aft EEqg3 |U0}}}}}6C=Bet 1 Jan 1900 and Jan 1901DR+19000101..+19010100..*0+=Lkly 1 Jan 1900D.+19000101.3+00000000..+/-=Appar 1 Jan 1900D.+19000101.2+00000000..+.-=Prhps 1 Jan 1900D.+19000101.1+00000000..+,-=Maybe 1 Jan 1900D.+19000101.?+00000000..#+=Jan 1900D.+19000100..+00000000..%*!=1 ??? 1900D.+19000001..+00000000..)=1900D.+19000000..+00000000..(('=1 Jan 1583/84D.+15830101/.+00000000.. '=1 JanD.+00000101..+00000000..&=JanD.+00000100..+00000000..(%'=1 Jan 1900 BCD.-19000101..+00000000..2$;=1 Jan 1900–5 Jan 1900D-+19000101..+19000105..#NULL.""=No DateD.+00000000..+00000000..6%1TThis is free text6=DS-005404C ]=From maybe 1 Apr 54BC to maybe 31 Dec 3 DS-00540401.?-00031231.?5A=1 Jan 1900 and 5 Jan 1900 DR+19000101..+19000105/.P=1 Jan 1900 DU+19000101..+00000000..(=1 Jan 1900 DT+19000101..+00000000..&!/1This is free textTThis is free text%!=1899/1900 D.+18990000/.+00000000..))=1 Jan 1949/50 D.+19490101/.+00000000.. ]] 6a:g:gXXXXX*0+=*1+=Poss 1 Jan 1900D.+19000101.4+00000000..*2+=Prob 1 Jan 1900D.+19000101.5+00000000..*3+=Cert 1 Jan 1900D.+19000101.6+00000000..)4)=Abt 1 Jan 1900D.+19000101.A+00000000..(5'=Ca 1 Jan 1900D.+19000101.C+00000000..)6)=Est 1 Jan 1900D.+19000101.E+00000000..*7+=Calc 1 Jan 1900D.+19000101.L+00000000..)8)=Say 1 Jan 1900D.+19000101.S+00000000..)9)=Aft 1 Jan 1900DA+19000101..+00000000..):)=Bef 1 Jan 1900DB+19000101..+00000000..*;+=From 1 Jan 1900DF+19000101..+00000000..+<-=Since 1 Jan 1900DI+19000101..+00000000..3===1 Jan 1900 or 5 Jan 1900DO+19000101..+19000105..8>G=Bet 1 Jan 1900 and 5 Jan 1900DR+19000101..+19000105..8?G=From 1 Jan 1900 to 5 Jan 1900DS+19000101..+19000105..(@'=To 1 Jan 1900DT+19000101..+00000000..+A-=Until 1 Jan 1900DU+19000101..+00000000..(B'=By 1 Jan 1900DY+19000101..+00000000..(C'=12da 5mo 1588Q.+15880512..+00000000..CD]=the first Wednesday after the big fire iTthe first Wednesday aft ]|Q'zO#M#]CD]=the first Wednesday after the big fire iTthe first Wednesday aft(C'=12da 5mo 1588Q.+15880512..+00000000..(B'=By 1 Jan 1900DY+19000101..+00000000..+A-=Until 1 Jan 1900DU+19000101..+00000000..(@'=To 1 Jan 1900DT+19000101..+00000000..8?G=From 1 Jan 1900 to 5 Jan 1900DS+19000101..+19000105..8>G=Bet 1 Jan 1900 and 5 Jan 1900DR+19000101..+19000105..3===1 Jan 1900 or 5 Jan 1900DO+19000101..+19000105..+<-=Since 1 Jan 1900DI+19000101..+00000000..*;+=From 1 Jan 1900DF+19000101..+00000000..):)=Bef 1 Jan 1900DB+19000101..+00000000..)9)=Aft 1 Jan 1900DA+19000101..+00000000..)8)=Say 1 Jan 1900D.+19000101.S+00000000..*7+=Calc 1 Jan 1900D.+19000101.L+00000000..)6)=Est 1 Jan 1900D.+19000101.E+00000000..(5'=Ca 1 Jan 1900D.+19000101.C+00000000..)4)=Abt 1 Jan 1900D.+19000101.A+00000000..*3+=Cert 1 Jan 1900D.+19000101.6+00000000..*2+=Prob 1 Jan 1900D.+19000101.5+00000000..*1+=Poss 1 Jan 1900D.+19000101.4+00000000..