×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

 Forum Search FAQs Links MVPs

## Other

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

Suppose we have a Table like this
tblEmployees
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"

#### CODE

CREATE TABLE Integers
(Num Long, CONSTRAINT Num PRIMARY KEY (Num))
Then Load it with the numbers from 0 to 9

#### CODE

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")

#### CODE

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.

#### CODE

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

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!

Already a member? Close this window and log in.