469,579 Members | 1,112 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

How to trim misc blanks within strings?

Any way to do this as part of a data cleansing routine?

One customer has supplied text that looks like this:
" 100 UF____________________16V_____________6555________ _____ELEC____________SM____"
Another row:
"___0.1UF______________16V____________0603________ _____X7R______________________"

Obviously, it would be much more desirable to store that as:
"100 UF 16V 6555 ELEC SM"
"0.1UF 16V 0603 X7R"

In Excel, the "trim" function removes all whitespace, converting it into single spaces, I believe.
In Access, it doesn't work that way... in fact, displaying the raw description and the TRIM(description), I actually can't even see a difference...

If it helps, I will be running this SQL as part of an VBA script that performs the data import. I'm pulling the data from Excel spreadsheets that have been pre-formatted.

EDIT: Pretend the underscore characters above are spaces!
In a frustratingly ironic twist of fate, THIS FORUM won't even let me post repeated spaces without truncating them down to one or two!
Not Funny. [takes 2 Advil]
Dec 2 '08 #1
5 6673
Please tell me something like THIS isn't the solution:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(DBimport.description,"----", "-"),"---","-"),"--","-"),"--","-"),"--","-"),"--","-"),"--","-")

I might have gone a little overboard - but that's part of what I'm trying to exemplify- why it doesn't seem like the right fix.

And, although even this might work 99% of the time, it's pretty Rube-Goldberg and has a finite limitation (although, 384 if I did my math right, for this particular combo)...

That's NOT elegant - there must be a "right" answer... Help me if you know it!
Dec 2 '08 #2
2,653 Expert 2GB
Hello, geolemon.

You could parse substrings into an array with Split() function, apply Trim() function on each array element and rejoin string with Join() function.
Dec 2 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
Hi Geolemon. Fish's suggestion on using the Split function is certainly one to follow up. I take a different approach to this, with the function below which returns a string where multiple occurrences of a particular character are replaced by single occurrences only. The default character replaced is the space, but any character can be specified.

Expand|Select|Wrap|Line Numbers
  1. Public Function fRemoveExcessChars(ByVal strInput, Optional ByVal strToReplace = " ") As String
  2. 'INPUT:  strInput - any string
  3. 'OUTPUT: a string in which all multiple occurrences of the character
  4. '        strToReplace have been replaced by single occurrences
  5.     Dim strChar As String, strResult As String
  6.     Dim intI As Integer, intL As Integer
  7.     Dim blLastCharReplaced As Boolean
  8.     blLastCharReplaced = False
  9.     if IsNull(strInput) then Exit Function 
  10.     intL = Len(strInput)
  11.     For intI = 1 To intL
  12.         strChar = Mid(strInput, intI, 1)
  13.         If strChar = strToReplace Then
  14.             If Not blLastCharReplaced Then
  15.                 If (intI <> 1) And (intI <> intL) Then
  16.                     strResult = strResult & strChar
  17.                 End If
  18.                 blLastCharReplaced = True
  19.             End If
  20.         Else
  21.             blLastCharReplaced = False
  22.             strResult = strResult & strChar
  23.         End If
  24.     Next intI
  25.     fRemoveExcessChars = strResult
  26. End Function
To use the function, place the code in any public code module (one which is visible from the Modules tab in Access) then add a calculated field to a query like this (in the access query editor):

NewText: fRemoveExcessChars([your field name])

Examples of use from VB Immediate Window:
Expand|Select|Wrap|Line Numbers
  1. ? fRemoveExcessChars("10uf               20V             Tantalum Bead")
  2. 10uf 20V Tantalum Bead
  3. ? fRemoveExcessChars(" 100 UF            16V              6555             ELEC SM ")
  4. 100 UF 16V 6555 ELEC SM
I use the function to pre-process user-entered strings where on occasions multiple spaces have been entered between words.

By the way, the Trim function in VBA is in effect a combination of the LTrim and RTrim functions - it removes any excess spaces from the left and right of a string. It does not remove multiple spaces within a string, unlike the Excel Trim function (to which my example above is equivalent).

Dec 2 '08 #4
Fish -

I'd have to do it row-by-row, stepping through the table, more VBA than SQL...
One description might have 2 words, another 12, with unpredictable amount of space in between. It's an option, just a bit more complicated than I had hoped for - I've really got to write an entire program around that.

Stewart -
And just as I say the above, you seem to have done all the work for me, lol.
Nice! And thanks!

I suppose I was hoping for a SQL function I could execute in a single-query-stroke... since I do wish to apply it to the entire table, rather than the entire column, but row-by-row.

However, the fact that you've gone through this already should probably tell me that I'm not the only one with this need, and there is NOT a simple, standard, best-practice function inside of SQL that would allow for this!

I wonder in that case if I can actulaly use the Excel "trim" function, invoked programatically, just prior to actually executing the VBA script that performs the TransferSpreadsheet function to import that data?
Dec 2 '08 #5
Stewart Ross
2,545 Expert Mod 2GB
If you are using Excel as an automation server from code running in Access you can use the WorksheetFunction method of the Application object to access the Trim function, possibly using code along these lines:

Expand|Select|Wrap|Line Numbers
  1. Dim objExcel as New Excel.Application
  3. <... code to open workbook etc ...>
  5. For i = 1 to lastrow
  6.     with objExcel.Activesheet.Cells(i, 1)
  7.       .value = objExcel.WorksheetFunction.Trim(.value)
  8.     End With
  9. next i
Dec 5 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Rohit Yogi | last post: by
8 posts views Thread by DrBob | last post: by
22 posts views Thread by Simon | last post: by
11 posts views Thread by Reply Via Newsgroup | last post: by
31 posts views Thread by rkk | last post: by
121 posts views Thread by swengineer001 | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.