WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
The following functions is interchangeable with the standard Trim , but will handle all of the white spaces. In fact, if you look closely, you will discover that it can be configured to trim off any character that you would like.
public Function TrimAll(ToTrim as string) as string Dim Start, Finish as Integer Dim ToEliminate as string ' Base condition test If len(ToTrim) = 0 then TrimAll = "" Exit Function End If ' Define the characters that we want to trim off ToEliminate = Chr(0) & Chr(8) & Chr(9) & Chr(10) & _ Chr(13) & Chr(32) ' Find the beginning of non-blank string Start = 1 While InStr(1, ToEliminate, mid$(ToTrim, Start, 1), _ vbTextCompare) <> 0 And Start <= len(ToTrim) Start = Start + 1 Wend ' Find the end of non-blank string Finish = len(ToTrim) While InStr(1, ToEliminate, mid$(ToTrim, Finish, 1), _ vbTextCompare) <> 0 And Finish > 1 Finish = Finish - 1 Wend ' ' If the string is completely blank, Start is going to be greater ' than Finish ' If Start > Finish then TrimAll = "" Exit Function End If ' Trim out the real contents TrimAll = mid$(ToTrim, Start, Finish - Start + 1) End Function