title: SQL Full Trim Function - Access/VBA code I can do it, but I don't know SQL Functions
body: <p>I need to combine fields and then trim out extra spaces the middle of a text field.  With Access it's easy for me but creating a SQL Function eludes me.  This is how I do it in Access, anyone able to help me create a SQL Function?</p>
  
  <p>In VBA Access Query I can do this with the following code in query:</p>
  
  <pre><code>FullTrim([tblLeadsResi].[House Number] &amp; [tblLeadsResi].[Street] &amp; " " &amp; 
      [tblLeadsResi].[Street Suffix] &amp; " " &amp; [tblLeadsResi].[Post-directional] &amp; 
      IIf(Not IsNull([tblLeadsResi].[Apartment Number])," #" &amp; 
      [tblLeadsResi].[Apartment Number],""))
  </code></pre>
  
  <p>Module Code in Access: (Basically if it's a double space it doesn't add the first space back in)</p>
  
  <pre><code>Public Function FullTrim(stText As String) As Variant
  Dim intLen As Integer, stPart As String, stBlank As String, stNewText As String
  
      '   ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
      '   ++++    Takes any spaces away from a Text Value     ++++
      '   ++++++++++++++++++++++++++++++++++++++++++++++++++++++++
  
      If IsNull(stText) Or stText = "" Then
          FullTrim = ""
      Else
          For intLen = 1 To (Len(stText) - 1)
              stPart = Mid(stText, intLen, 1)
              stBlank = Mid(stText, intLen, 2)
              If stBlank &lt;&gt; "  " Then
                  stNewText = stNewText &amp; stPart
              End If
          Next intLen
  
          intLen = Len(stText)
          stPart = Mid(stText, intLen, 1)
          stNewText = stNewText &amp; stPart
  
          stNewText = Trim(stNewText)
          FullTrim = stNewText
      End If
  End Function
  </code></pre>