SQL String manipulation

[TRACEYDEV]

I have the following data that comes out as

1.001.06
1.001.06.1
1.001.06.1.030
1.001.06.1.031
1.001.06.D
1.001.06.D.030
1.001.06.D.031

the format could sometimes be 1.001 or 1.001.06 up to 5 levels

What i need to do when looping the records is store them into a variable
1.001.06 =
$var1 = 1
$var2 = 001
$var3 = 06
$var4 =
$var5 =

1.001.06.1 =
$var1 = 1
$var2 = 001
$var3 = 06
$var4 = 1
$var5 =

and depending on the values create

1.001.06 becomes 9.01.006.000.000
1.001.06.1 9.01.006.001.000
1.001.06.1.030 9.01.006.001.030
1.001.06.1.031 9.01.006.001.031
1.001.06.D 9.01.006.00D.000

where the mapping is
x.xx.xxx.xxx.xxx

so if i only have
1.001.06 then i end up with 9.01.006.000.000

Any suggestions

[Benjamin]

Do you want to this in SQL or o you use also VB?

[TRACEYDEV]

SQL
Thanks

[Benjamin]

This is not that simple I assume, you have to use the instr() to define the position of the . and the left, mid, right functions to get the strings.

With Vb it would be easier, with only 1 SQL statement... I don't know.