Strip out multiple space separated strings from field

I was able to do this with VBA.

1) Add this function to a regular VBA module:

Option Explicit
Option Compare Database

Function RetrieveSplitItem(Index As Long, Expression As Variant, Optional Delimiter As String = " ", _
    Optional Limit As Long = -1, Optional Compare As VbCompareMethod = vbTextCompare, _
    Optional FailIfNoDelimiter As Boolean = False) As Variant
    
    ' Function by Patrick G. Matthews
    
    ' This function is a wrapper for the VB6/VBA Split function, thus allowing it to be used in
    ' the user interface for Excel and/or Access.  Depending on the Index value, the function
    ' returns a single delimited value, or an array of the various delimited values
    
    ' Arguments:
    
    ' Index indicates which element to return.  If zero, function returns the full array.  If >0,
    ' function returns the Nth delimited item, starting with 1 (even though the lower bound of
    ' the array is zero).  If <0, function returns the Nth to last delimited item.  If the
    ' absolute value of Index is greater than the number of elements in the array, function
    ' returns a subscript out of range error
    
    ' Expression is the delimited string expression to be split
    
    ' Delimiter is the character/sequence of characters used to delimit the string expression
    
    ' Limit places a cap on the number of elements returned by the function.  Default -1
    ' indicates that there is no limit
    
    ' Compare indicates whether to match the Delimiter as non-case-sensitive (default, or 1),
    ' or case-sensitive (vbBinaryCompare from VBA, or 0)
    
    ' FailIfNoDelimiter controls how function behaves if the Delimiter is not found.  Default
    ' (false) is to use the regular Split functionality, and still return an array with a
    ' single element so long as the string expression is not a zero length string.  If True,
    ' if the Delimiter is not found then the function returns a subscript out of range error
    
    Dim arr As Variant
    
    ' Set default return value of the function.  9 corresponds to 'subscript out of range'
    
    RetrieveSplitItem = CVErr(9)
    
    ' Check for presence of Delimiter
    
    If Not (InStr(1, Expression, Delimiter, Compare) = 0 And FailIfNoDelimiter) Then
        
        ' Create initial array using Split
        
        arr = Split(Expression, Delimiter, Limit, Compare)
        
        ' Depending on Index, choose the appropriate return value
        
        Select Case Index
            
            ' For Index = 0, just return the whole array
            
            Case 0
                RetrieveSplitItem = arr
            
            ' If positive, return the Nth element of the array.  If Index is too large,
            ' return an error value
            
            Case Is > 0
                If (Index - 1) <= UBound(arr) Then
                    RetrieveSplitItem = arr(Index - 1)
                End If
            
            ' If negative, return the Nth to last element of the array.  If absolute value
            ' of Index is too large, return an error value
            
            Case Else
                If (UBound(arr) + Index + 1) >= 0 Then
                    RetrieveSplitItem = arr(UBound(arr) + Index + 1)
                End If
        End Select
    End If
    
End Function

Select allOpen in new window

2) Use a query like this:

SELECT SomeTable.ID, SomeTable.BaseData, 
    IIf(IsError(RetrieveSplitItem(1,[BaseData]," ")),"",RetrieveSplitItem(1,[BaseData]," ")) AS Expr1, 
    IIf(IsError(RetrieveSplitItem(2,[BaseData]," ")),"",RetrieveSplitItem(2,[BaseData]," ")) AS Expr2, 
    IIf(IsError(RetrieveSplitItem(3,[BaseData]," ")),"",RetrieveSplitItem(3,[BaseData]," ")) AS Expr3, 
    IIf(IsError(RetrieveSplitItem(4,[BaseData]," ")),"",RetrieveSplitItem(4,[BaseData]," ")) AS Expr4, 
    IIf(IsError(RetrieveSplitItem(5,[BaseData]," ")),"",RetrieveSplitItem(5,[BaseData]," ")) AS Expr5
FROM SomeTable

Select allOpen in new window

I demonstrate this in the attached file.
Q-27541208.mdb

Tested and working:


SELECT SUBSTRING(ColumnName, 1, CHARINDEX(' ', ColumnName)) AS [First],
SUBSTRING(ColumnName, CHARINDEX(' ', ColumnName) + 2, LEN(ColumnName)) AS [Second],
SUBSTRING(ColumnName, CHARINDEX(' ', ColumnName) + 3, LEN(ColumnName)) AS [Third]
--add more columns by increasing the count to +4, +5 and so on.
from TableNae

replace the table and column names to yours above.

@viralypatel,

Here is the SQL view of my query.  The second through fifth column would have he stripped strings in order.  The second column was my attempt at getting the first left string out, it would be replaced with your statement.

SELECT [Mfg Part Master].PRTNUM_49, Left([PMDES1_01],InStr([PMDES1_01]," ")) AS Expr1, [Part Master].PMDES1_01, [Part Master].PMDES1_01, [Part Master].PMDES1_01, [Part Master].PMDES1_01, [Mfg Part Master].MPNNUM_49, [Mfg Part Master].MPNMFG_49, [Part Master].COMCDE_01 INTO tblPbFreeParts
FROM [Part Master] INNER JOIN [Mfg Part Master] ON [Part Master].PRTNUM_01 = [Mfg Part Master].PRTNUM_49
WHERE ((([Part Master].COMCDE_01)="CAP" Or ([Part Master].COMCDE_01)="CON" Or ([Part Master].COMCDE_01)="DIO" Or ([Part Master].COMCDE_01)="FER" Or ([Part Master].COMCDE_01)="FUS" Or ([Part Master].COMCDE_01)="ICS" Or ([Part Master].COMCDE_01)="IND" Or ([Part Master].COMCDE_01)="LED" Or ([Part Master].COMCDE_01)="OSC" Or ([Part Master].COMCDE_01)="PRG" Or ([Part Master].COMCDE_01)="RES" Or ([Part Master].COMCDE_01)="RNS" Or ([Part Master].COMCDE_01)="SOC" Or ([Part Master].COMCDE_01)="SWT") AND (([Mfg Part Master].PREFER_49)<>"9"));


How would I fit your SELECT statement into it?

gibneyt,

Did you even try my suggestion?

Patrick

matthewspatrick,

I apologize.  No offense intended.  It is a matter of my capabilities; jack of all trades, master of none.  I do not know how to implement it. How do I add this to a regular VBA module in Access 2007?   And then how would I incorporate your query into mine?

Tim

matthewspatrick,

OK, I got the code into a module.  How do I incorporate your query into what I already have?  It is listed above.

Tim

Tim,

Which version of the code did you use, the first version (from http:#a37450601), or the second (from http:#a37450642)?

If the former:

SELECT m.PRTNUM_49, 
    IIf(IsError(RetrieveSplitItem(1,[PMDES1_01]," ")),"",RetrieveSplitItem(1,[PMDES1_01]," ")) AS Expr1, 
    IIf(IsError(RetrieveSplitItem(2,[PMDES1_01]," ")),"",RetrieveSplitItem(2,[PMDES1_01]," ")) AS Expr2, 
    IIf(IsError(RetrieveSplitItem(3,[PMDES1_01]," ")),"",RetrieveSplitItem(3,[PMDES1_01]," ")) AS Expr3, 
    IIf(IsError(RetrieveSplitItem(4,[PMDES1_01]," ")),"",RetrieveSplitItem(4,[PMDES1_01]," ")) AS Expr4, 
    IIf(IsError(RetrieveSplitItem(5,[PMDES1_01]," ")),"",RetrieveSplitItem(5,[PMDES1_01]," ")) AS Expr5, 
    m.MPNNUM_49, 
    m.MPNMFG_49, 
    p.COMCDE_01 
INTO tblPbFreeParts
FROM [Part Master] p INNER JOIN 
    [Mfg Part Master] m ON p.PRTNUM_01 = m.PRTNUM_49
WHERE (((p.COMCDE_01)="CAP" Or (p.COMCDE_01)="CON" Or (p.COMCDE_01)="DIO" Or (p.COMCDE_01)="FER" Or (p.COMCDE_01)="FUS" Or (p.COMCDE_01)="ICS" Or (p.COMCDE_01)="IND" Or (p.COMCDE_01)="LED" Or (p.COMCDE_01)="OSC" Or (p.COMCDE_01)="PRG" Or (p.COMCDE_01)="RES" Or (p.COMCDE_01)="RNS" Or (p.COMCDE_01)="SOC" Or (p.COMCDE_01)="SWT") AND ((m.PREFER_49)<>"9"));

Select allOpen in new window

If the latter:

SELECT m.PRTNUM_49, 
    RetrieveSplitItem(1,[PMDES1_01]," ") AS Expr1, 
    RetrieveSplitItem(2,[PMDES1_01]," ") AS Expr2, 
    RetrieveSplitItem(3,[PMDES1_01]," ") AS Expr3, 
    RetrieveSplitItem(4,[PMDES1_01]," ") AS Expr4, 
    RetrieveSplitItem(5,[PMDES1_01]," ") AS Expr5, 
    m.MPNNUM_49, 
    m.MPNMFG_49, 
    p.COMCDE_01 
INTO tblPbFreeParts
FROM [Part Master] p INNER JOIN 
    [Mfg Part Master] m ON p.PRTNUM_01 = m.PRTNUM_49
WHERE (((p.COMCDE_01)="CAP" Or (p.COMCDE_01)="CON" Or (p.COMCDE_01)="DIO" Or (p.COMCDE_01)="FER" Or (p.COMCDE_01)="FUS" Or (p.COMCDE_01)="ICS" Or (p.COMCDE_01)="IND" Or (p.COMCDE_01)="LED" Or (p.COMCDE_01)="OSC" Or (p.COMCDE_01)="PRG" Or (p.COMCDE_01)="RES" Or (p.COMCDE_01)="RNS" Or (p.COMCDE_01)="SOC" Or (p.COMCDE_01)="SWT") AND ((m.PREFER_49)<>"9"));

Select allOpen in new window

Patrick

matthewspatrick,

The latter, and the code works great!  Thanks so much.  A few q's.

I can change "Expr1" and the others to anything I like?
If I see empty column(s) between columns with data within any given row I can assume there must be two (or more) spaces?
To add a sixth and successive expressions, if necessary, I can simply add more lines similar to lines 2 through 6 above?

Again, apologies and thanks.  Beautiful code!

Tim

matthewspatrick,

Thanks very much for the brilliant code.

Tim

Tim,

Glad to help :)

>>I can change "Expr1" and the others to anything I like?

Yes.  If you want to use spaces/punctuation, you'll need square brackets, though, like this: [My Column Name]

>>If I see empty column(s) between columns with data within any given row I can assume there must be two (or more) spaces?

Yes.  Two consecutive spaces will be treated as two column breaks.

>>To add a sixth and successive expressions, if necessary, I can simply add more lines similar to lines 2 through 6 above?

Yes

>>Again, apologies and thanks.  Beautiful code!

Glad you liked it!

Cheers,

Patrick