[AccessD] Tip: Replacement for Split in SQL

Hi all

In SQL Split cannot be used as SQL doesn't accept a syntax to retrieve a single value from the array Split returns.

But if you have a field with a content like:

  xyz.001.1234.ab 

you can simulate Split like this to split the content into its four parts:

  SELECT 
    Code, 
    InStr(1,[Code],".") AS Dot1, 
    InStr(1+[Dot1],[Code],".") AS Dot2, 
    InStr(1+[Dot2],[Code],".") AS Dot3, 
    Mid([Code],1,[Dot1]-1) AS Col1, 
    Mid([Code],1+[Dot1],[Dot2]-[Dot1]-1) AS Col2, 
    Mid([Code],1+[Dot2],[Dot3]-[Dot2]-1) AS Col3, 
    Mid([Code],1+[Dot3]) AS Col4
  FROM 
    tblSomeTable;

It can easily be expanded to return more columns.

/gustav