openSource

Got a request to create a data table get from some existing data to some required data formatting. The Source data is like 'C10 L1_B21773197D.xls', 'EV1 L1_B21773602F.xls', 'EV116 L1_B21773659G.xls', etc. The following were the requirements. CategoryCode No NVARCHAR(10) C Contained in legacy data file name Sample legacy data file name : “ C 10 L1_B21773197D ” 1.        Alphabets prior to Numeric characters) FolderNo No VARCHAR(10) C001 Contained in legacy data file name Sample legacy data file name : “ C 10 L1_B21773197D ” 1.        convert numeric characters prior to “white space” to a 3 digit number (e.g. “1” --> “001”) 2.        Append 3 digit number to Category Code VolumeNo No VARCHAR(10) C001L01 Contained in legacy data file name Sample legacy data file name : “ C10 L1_B21773197D ” 1.        Extract the 2 nd occurrence of an Alphabets in Fil