Extracting values from a field value in a table
Posted on 2010-01-04
I have the following table
(field1,field2, field 3, field4, field5) and assuming filed1 as a primary key and the values for field2 has a special property that for each row filed2=field3filed4field5. For example for a given row if we have the value of fiedld2 as A04UV then it means that the value for filed3 is A , the value for field4 is 04 and the value for field5 is UV . The values for field5 and field3 are always non numeric and can have a length of either 1 or 2 or they can be NULL. However, the value of field 4 has the following property
=>The length of the value for field4 can always be 2. In other words field4 values are of the form 0X, where x any number between 1 to 9 or of the form xy where both x and y are between 1 and 9.
So the question is :
The table is created from a datasource which only had values for field1, field2 and I want to create another table for which the values of filed3,field4 and field5 are extracted from the value of field2 for each row in table . The values for filed1 and field2 can be the same in the new table what I want is to extract the values for filed3,filed4 and field5 from field2 based on their relationships described above. I dont want a c# or vb.net code what I want is to do this in SQL