I need to create a view based upon a SQL table that contains 2 string fields (Job, Serial) i.e. one job contains many serials in this table.
The Serial string consists of 4 alpha then 5 numeric. (e.g. ABDC12345)
The resulting View needs to have the following fields (Job, Start Serial, End Serial) for each Job number
Where Start Serial consists of the 4 alpha & the lowest 5 numeric for the given Job
Where End Serial consists of the 4 alpha & the highest 5 numeric for the given job
Any preceding zeros in the 5 numeric portion of the Serial string need to be maintained when the numeric portion is joined back to the resulting Start Serial & End Serial strings)
i.e. ABCD00001 not ABDC1 (resulting string must always be 9 chars in length)