T-SQL trim &nbsp (and other non-alphanumeric characters)

Question

We have some input data that sometimes appears with &nbsp characters on the end.

The data comes in from the source system as varchar() and our attempts to cast as decimal fail b/c of these characters.

Ltrim and Rtrim don't remove the characters, so we're forced to do something like:

UPDATE myTable
SET myColumn = replace(myColumn,char(160),'')
WHERE charindex(char(160),myColumn) > 0

This works for the &nbsp, but is there a good way to do this for any non-alphanumeric (or in this case numeric) characters?

Answer 1

This page has a sample of how you can remove non-alphanumeric chars:

-- Put something like this into a user function:
DECLARE @cString    VARCHAR(32)
DECLARE @nPos    INTEGER
SELECT  @cString = '90$%45623 *6%}~:@'
SELECT  @nPos = PATINDEX('%[^0-9]%', @cString)

WHILE @nPos > 0
BEGIN
SELECT @cString = STUFF(@cString, @nPos, 1, '')
SELECT  @nPos = PATINDEX('%[^0-9]%', @cString)
END

SELECT @cString

Answer 2

How is the table being populated? While it is possible to scrub this in sql a better approach would be to change the column type to int and scrub the data before it's loaded into the database (SSIS). Is this an option?

Answer 3

This will remove all non alphanumeric chracters

CREATE FUNCTION [dbo].[fnRemoveBadCharacter]
(
    @BadString nvarchar(20)
)
RETURNS nvarchar(20)
AS
BEGIN

            DECLARE @nPos INTEGER
            SELECT @nPos = PATINDEX('%[^a-zA-Z0-9_]%', @BadString)

            WHILE @nPos > 0
            BEGIN
                        SELECT @BadString = STUFF(@BadString, @nPos, 1, '')
                        SELECT @nPos = PATINDEX('%[^a-zA-Z0-9_]%', @BadString)
            END

            RETURN @BadString
END

Use the function like:

UPDATE TableToUpdate
SET ColumnToUpdate = dbo.fnRemoveBadCharacter(ColumnToUpdate)
WHERE whatever