Re: Is there a way (VBA) Sekection in Array
@FormR
I have a complex SQL statement to use on a string to generate a date from an encoded driving licence
,CASE WHEN LEN(replace(replace(replace(replace(upper(tL.LICENCE_NUMBER),'XX',''),'XXX',''),'**',''),'***','')) = 9 THEN '-X-' WHEN LEN(replace(replace(replace(replace(upper(tL.LICENCE_NUMBER),'XX',''),'XXX',''),'**',''),'***','')) = 8 THEN '-X-' WHEN LEN(replace(replace(replace(replace(upper(tL.LICENCE_NUMBER),'XX',''),'XXX',''),'**',''),'***','')) = 10 THEN '-X-' WHEN LEN(replace(replace(replace(replace(upper(tL.LICENCE_NUMBER),'XX',''),'XXX',''),'**',''),'***','')) = 12 THEN '-X-' WHEN Convert(varchar(10),PRP.DOFB,3) <> SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),9,2)+'/'+case when isnumeric(SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1)) = 1 then replace(REPLACE(SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1),'5','0') ,'6','1') else SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1) end +''+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),8,1)+'/'+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),6,1)+''+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),11,1) then SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),9,2)+'/'+case when isnumeric(SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1)) = 1 then replace(REPLACE(SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1),'5','0') ,'6','1') else SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),7,1) end +''+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),8,1)+'/'+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),6,1)+''+SUBSTRING(replace(tL.LICENCE_NUMBER,' ',''),11,1) else '' end as [DL Date]
it generates some values like
"(D/03/4V", "//", "12/95/E1", "15/19/95", "32/-2/09", "32/80/76", "61/40/07", "9E/09/NJ", "C1/No/-L", "GV/E9/N5", "-X-"
Post 7 above does mostly what I need apart from its including BLANKS in the selection (which I need to exclude) for cleaning and reporting purposes