Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Queries and JET SQL FAQ


Parsing Delimited Fields in SQL by Golom
Posted: 4 May 06 (Edited 4 May 06)

Suppose we have a Table like this
  EmployeeID      Children

     123          Sally,Mary,Tom
     234          William,Todd

and we want to see
  EmployeeID      Child

     123          Sally
     123          Mary
     123          Tom
     234          William
     234          Todd

First, Create an "Integers" table with one field "Num"


Then Load it with the numbers from 0 to 9


INSERT INTO Integers (Num) Values(0)
INSERT INTO Integers (Num) Values(1)
INSERT INTO Integers (Num) Values(9)
Then create a query (let's call it "qryInt2")


Select 10*I1.Num + I2.Num As [N]
From   Integers I1, Integers I2
... and now we're finally ready. All that building an integers table stuff (above) is something that needs to be done only once.


SELECT EmployeeID,
       Mid("," & E.Children & ",",
            S1.N + 1 ,
            S2.N - S1.N - 1) AS [Child]

FROM tblEmployees AS E, qryInt2 AS S1, qryInt2 AS S2

WHERE Mid$("," & E.Children & "," ,S1.N , 1 ) = ','
  and Mid$("," & E.Children & "," ,S2.N , 1 ) = ','
  and S1.N > 0 AND S2.N > 0
  and S2.N <= Len(E.Children) + 2
  and S1.N + 1 < S2.N
  and InStr(1,Mid("," & E.Children & ",",
                        S1.N + 1 ,
                        S2.N - S1.N - 1),",")=0

ORDER BY 1, 2;
So what's going on here?

First the FROM clause
This clause does a cross join of three tables: the original employee table and TWO copies of the qryInt2 query. That will produce
2 * 100 * 100 = 20,000 records.

Now the WHERE clause that does all the work

      Mid$("," & E.Children & "," ,S1.N , 1 ) = ','
  and Mid$("," & E.Children & "," ,S2.N , 1 ) = ','

These conditions require that any string returned by the query starts and ends with a comma. That's why we attached commas before and after the field.

and S1.N > 0 AND S2.N > 0
Character counting starts at 1 so we eliminate the zeros

and S2.N <= Len(E.Children) + 2
we don't need to look past the end of the field where there are no characters

and S1.N + 1 < S2.N
S1.N is the start of the string we are extracting and S2.N is the end and the start must always be before the end.

and InStr(1,Mid("," & E.Children & ",", S1.N + 1 , S2.N - S1.N - 1),",")=0
we want only those strings that start and end with a comma but do not have any commas inside them.

and finally, the SELECT

Mid("," & E.Children & ",", S1.N + 1 , S2.N - S1.N - 1)
Pulls out the substrings within E.Children. The only ones remaining after WHERE has done its work are the names we want.

Cautionary Notes:
  • Obviously the task of reducing 20,000 records down to 5 (as in this example) is time consuming. It is probably best to use this to create normalized tables from un-normalized ones. I would not suggest using it as something you run every day.
  • The "qryInt2" table needs to have enough values in it to span the length of the longest string in the field you are parsing plus 2. If you have long strings (more than 97 characters in our example) then you would need to supply a table or query with enough values to span the string.

  • Credit to Joe Celko who originally developed this SQL. My contribution was just to convert it to MS/Access SQL.

    Back to Microsoft: Access Queries and JET SQL FAQ Index
    Back to Microsoft: Access Queries and JET SQL Forum

    My Archive

    Close Box

    Join Tek-Tips® Today!

    Join your peers on the Internet's largest technical computer professional community.
    It's easy to join and it's free.

    Here's Why Members Love Tek-Tips Forums:

    Register now while it's still free!

    Already a member? Close this window and log in.

    Join Us             Close