Monday, August 15, 2011

SQL function to trim all including char 160

The SQL standard functions provided in MsSQL LTrim() and RTrim() if used together should supposedly trim away both leading and trailing white spaces and tabs. However, it seems Char(160), newlines and tabs are ignored from the triming "exercise".

The following custom function provides a fix to this annoying "bug".
CREATE FUNCTION TrimAll (@s VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN RTrim(LTrim(Replace(Replace(Replace(Replace(@s, Char(160), ' '), Char(10), ''), Char(13), ''), Char(9), '')))
END
GO

To use, simply do something like this
SELECT dbo.TrimAll(MyField) FROM MyTable

1 comment:

  1. Use below if want to replace tabs as well.

    CREATE FUNCTION TrimAll (@s VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
    AS
    BEGIN
    RETURN RTrim(LTrim(Replace(Replace(badword, Char(160), ' '), Char(9), '')))
    END
    GO

    ReplyDelete

Do provide your constructive comment. I appreciate that.