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.

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

Advertisement

Answer