INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a Computer / IT professional? Join TekTips Forums!
 Talk With Other Members
 Be Notified Of Responses
To Your Posts
 Keyword Search
 OneClick Access To Your
Favorite Forums
 Automated Signatures
On Your Posts
 Best Of All, It's Free!
*TekTips's functionality depends on members receiving email. By joining you are opting in to receive email.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Microsoft: Access Queries and JET SQL FAQ
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"
CODECREATE TABLE Integers (Num Long, CONSTRAINT Num PRIMARY KEY (Num)) Then Load it with the numbers from 0 to 9
CODEINSERT 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")
CODESelect 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.
CODESELECT 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 unnormalized 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 


