splitting data from one column into multiple columns

[BECAM]

I have a database which includes a table of carton descriptions. The table
has two columns - ID and Description (as below)

ID Description
1 02*936 + 01*921 + 12*854
2 12*954 + 05*521
3 09*712 + 09*713 + 01*254 + 02*255
etc

Is there a query that i can run that can use the "+" symbol to split the
field in the table into individual columns in a query

ie. ID Col1 Col2 Col3 Col4
1 02*936 01*921 12*854
2 12*954 05*521
3 09*712 09*713 01*254 02*255

 

[Allen Browne]

The most efficient way to do this will be to use VBA code to parse the
Desription, and assign the values to the fields.

However, if you want to do it with a query, you could copy the code below
into a standard module (New on Modules tab of Database window). Then create
an Update query (Update on Query menu in query design). In the Update row
under Col1, enter:
SplitToCol([Description], "+", 1)
and under Col2:
SplitToCol([Description], "+", 2)
and so on.

Limitations of this approach:
a) Requires Access 2000 or later.
b) Will be slow (because the function is called multiple times for each
record, and fully parses the description repeatedly.)

Function SplitToCol(varInput As Variant, strDelim As String, _
ByVal intItem As Integer) As Variant
'Purpose: Parse the n-th item from the input string.
'Arguments: varInput the list of items as a string.
' strDelim = the delimiter character(s) between items.
' intItem = which item you want from the string (1 for first,
....)
'Return: one item from the delimited string. Null on error.
'Example: This returns "cat", i.e. the 2nd item:
' SplitToCol("dog, cat, bird", ",", 2)
'Note: Leading and trailing spaces are trimmed.

Dim varArray As Variant 'Input as an array
Dim varReturn As Variant 'Return value

varReturn = Null 'Initialize to Null
intItem = intItem - 1 'Assume zero-based array.

If Not IsError(varInput) Then
If Len(varInput) > 0 Then
varArray = Split(varInput, strDelim)
If IsArray(varArray) Then
If intItem >= LBound(varArray) And _
intItem <= UBound(varArray) Then
varReturn = Trim(varArray(intItem))
If Len(varReturn) = 0 Then
varReturn = Null
End If
End If
End If
End If
End If

SplitToCol = varReturn
End Function

 

[peregenem]

The most efficient way to do this will be to use VBA code to parse the
Desription, and assign the values to the fields.

Moot point e.g. run time, code maintenance, learning curve, etc?

However, if you want to do it with a query <<snip>>

Your 'query' approach still uses a procedural mindset and VBA code.

For a pure set based approach, you can do the parsing using SQL. Here's
an
example by my pal Celko, translated into Access/Jet (it may be best to
construct the Sequence table in Excel <g>). Note the delimiting
character in this example is the more commonly encountered comma:

CREATE TABLE InputStrings (
keycol VARCHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL)
;
INSERT INTO InputStrings
VALUES ('first', '12,34,567,896')
;
INSERT INTO InputStrings
VALUES ('second', '312,534,997,896')
;
INSERT INTO InputStrings
VALUES ('Third', '667,841')
;
INSERT INTO InputStrings
VALUES ('Forth', '523,842,225,227,458,369')
;
CREATE TABLE Parmlist (
keycol VARCHAR(10) NOT NULL,
parm INTEGER NOT NULL)
;
CREATE TABLE Sequence (seq INTEGER NOT NULL)
;
INSERT INTO Sequence VALUES (1)
;
INSERT INTO Sequence VALUES (2)
;
INSERT INTO Sequence VALUES (3)
;
....
INSERT INTO Sequence VALUES (999)
;
INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CLNG(MID$(I1.input_string, S1.seq, MIN(S2.seq) - S1.seq - 1))
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE MID$(',' & I1.input_string & ',', S1.seq, 1) =
','
AND MID$(',' & I1.input_string & ',', S2.seq, 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq
;