Custom String Conversion

I want to convert a string that can have two formats.

Example

• 1090512300 to 9.5.123
• 1090501300 to 9.5.13

The first string (1090512300) will always have the same length but the second(9.5.123) string won’t.

The conversion logic is as follows 1 xx yy zzz 00
So string 1 will be 1xxyyzzz00 and string 2 xx.yy.zzz where all zeros have been removed.

I tried using a number formatting but it doesn’t work correctly due to the fact string 2 won’t have the same length every time.

10000

I would prefer a SQL function that can perform this conversion but I do not know how this will be done.

I came up with the following code in C#.

How would this be translated to either VBA or SQL.

private static void Main(string[] args)
    {
        oldToNew("7.1.14");
        oldToNew("9.5.123");
        oldToNew("9.5.13");
        oldToNew("12.1.10");
        oldToNew("12.10.10");
        Console.ReadLine();

        NewToOld("1050126000");
        NewToOld("1060104900");
        NewToOld("1060105000");
        NewToOld("1070414200");
        Console.ReadLine();
    }
    static public void oldToNew(string Code)
    {
        string a, b, c, manCode;
        manCode = Code;
        a = manCode.Substring(0, manCode.IndexOf("."));
        manCode = manCode.Remove(0, manCode.IndexOf(".") + 1);
        b = manCode.Substring(0, manCode.IndexOf("."));
        manCode = manCode.Remove(0, manCode.IndexOf(".") + 1);
        c = manCode.Substring(0);
        a = Convert.ToInt16(a).ToString("D2");
        b = Convert.ToInt16(b).ToString("D2");
        c = Convert.ToInt16(c).ToString("D3");

        manCode = "1" + a + b + c + "00";
        Console.WriteLine("Converted t" + Code + "tt:t" + manCode);
    }

    static public void NewToOld(string Code)
    {
        string a, b, c, manCode;
        manCode = Code;
        manCode = manCode.Remove(0, 1);
        a = Convert.ToInt16(manCode.Substring(0, 2)).ToString();
        manCode = manCode.Remove(0, 2);
        b = Convert.ToInt16(manCode.Substring(0, 2)).ToString();
        manCode = manCode.Remove(0, 2);
        c = Convert.ToInt16(manCode.Substring(0, 3)).ToString();
        manCode = a + "." + b + "." + c;
        Console.WriteLine("Converted t" + Code + "t:t" + manCode);
    }

Answer

With MSAccess I would probably use MID, CINT AND CSTR:

SELECT cStr(cInt(Mid(OldFormat, 2, 2))) + "." +
       cStr(cInt(Mid(OldFormat, 4, 2))) + "." + 
       cStr(cInt(Mid(OldFormat, 6, 3))) As NewFormat
FROM TableName