SQL Full Trim Function - Access/VBA code I can do it, but I don't know SQL Functions

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?

In VBA Access Query I can do this with the following code in query:

FullTrim([tblLeadsResi].[House Number] & [tblLeadsResi].[Street] & " " & 
    [tblLeadsResi].[Street Suffix] & " " & [tblLeadsResi].[Post-directional] & 
    IIf(Not IsNull([tblLeadsResi].[Apartment Number])," #" & 
    [tblLeadsResi].[Apartment Number],""))

Module Code in Access: (Basically if it's a double space it doesn't add the first space back in)

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 <> "  " Then
                stNewText = stNewText & stPart
            End If
        Next intLen

        intLen = Len(stText)
        stPart = Mid(stText, intLen, 1)
        stNewText = stNewText & stPart

        stNewText = Trim(stNewText)
        FullTrim = stNewText
    End If
End Function

Answers


Creation

CREATE FUNCTION dbo.FullTrim(@Value NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Subs NVARCHAR(6)
    SET @Subs = '~@$$#%' -- Make this some string you will never have in your data
    RETURN LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@Value, '  ', ' ' + @Subs), @Subs + ' ', ''), @Subs, '')))
END

Usage

SELECT dbo.FullTrim('  This is a     string      with    many spaces ')

Result

This is a string with many spaces

Try this function:

CREATE FUNCTION [dbo].[FullTrim](@text nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    RETURN replace(@text, ' ', '')
END

...and then pass in all your fields concatenated together like:

SELECT dbo.FullTrim([tblLeadsResi.House Number] + [tblLeadsResi.Street] + ...) 
FROM tblLeadsResi
...

Need Your Help

What key was pressed? Keyboard hooks

c++ winapi hook

I'm using low level hooks, but I can't determine what key was pressed. Values are the same for every single key. Is here something I'm doing wrong?

SQLAlchemy hangs while connecting to SQL Azure, but not always

python django sqlalchemy azure-sql-database pyodbc

I have a django application, which is making use of SQLAlchemy to connect to a SQL Server instance on Windows Azure. The app has worked perfectly for 3 months on a local SQL Server instance, and for