<p>We have some input data that sometimes appears with &amp;nbsp characters on the end.</p>

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

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

<pre><code>UPDATE myTable
SET myColumn = replace(myColumn,char(160),'')
WHERE charindex(char(160),myColumn) &gt; 0
</code></pre>

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

[{'answer_id': 52327, 'author': 'Espo', 'author_id': 2257, 'author_profile': 'https://Stackoverflow.com/users/2257', 'pm_score': 4, 'selected': True, 'text': '<p><a href="http://www.lazydba.com/sql/1__4390.html" rel="noreferrer">This page</a> has a sample of how you can remove non-alphanumeric chars:</p>\n\n<pre><code>-- Put something like this into a user function:\nDECLARE @cString    VARCHAR(32)\nDECLARE @nPos    INTEGER\nSELECT  @cString = \'90$%45623 *6%}~:@\'\nSELECT  @nPos = PATINDEX(\'%[^0-9]%\', @cString)\n\nWHILE @nPos &gt; 0\nBEGIN\nSELECT @cString = STUFF(@cString, @nPos, 1, \'\')\nSELECT  @nPos = PATINDEX(\'%[^0-9]%\', @cString)\nEND\n\nSELECT @cString \n</code></pre>\n'}, {'answer_id': 52348, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 1, 'selected': False, 'text': "<p>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?</p>\n"}, {'answer_id': 879018, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 4, 'selected': False, 'text': "<p>This will remove all non alphanumeric chracters</p>\n\n<pre><code>CREATE FUNCTION [dbo].[fnRemoveBadCharacter]\n(\n    @BadString nvarchar(20)\n)\nRETURNS nvarchar(20)\nAS\nBEGIN\n\n            DECLARE @nPos INTEGER\n            SELECT @nPos = PATINDEX('%[^a-zA-Z0-9_]%', @BadString)\n\n            WHILE @nPos &gt; 0\n            BEGIN\n                        SELECT @BadString = STUFF(@BadString, @nPos, 1, '')\n                        SELECT @nPos = PATINDEX('%[^a-zA-Z0-9_]%', @BadString)\n            END\n\n            RETURN @BadString\nEND\n</code></pre>\n\n<p>Use the function like:</p>\n\n<pre><code>UPDATE TableToUpdate\nSET ColumnToUpdate = dbo.fnRemoveBadCharacter(ColumnToUpdate)\nWHERE whatever\n</code></pre>\n"}, {'answer_id': 28970617, 'author': 'BrandonNeiger', 'author_id': 4655121, 'author_profile': 'https://Stackoverflow.com/users/4655121', 'pm_score': 0, 'selected': False, 'text': "<p>For large datasets I have had better luck with this function that checks the ASCII value. I have added options to keep only alpha, numeric or alphanumeric based on the parameters.</p>\n\n<pre><code>--CleanType 1 - Remove all non alpanumeric\n--          2 - Remove only alpha\n--          3 - Remove only numeric\nCREATE FUNCTION [dbo].[fnCleanString] (\n        @InputString    varchar(8000)\n    ,   @CleanType      int \n    ,   @LeaveSpaces    bit \n)   RETURNS varchar(8000)\nAS \nBEGIN\n\n    -- // Declare variables\n    -- ===========================================================\n    DECLARE @Length     int\n        ,   @CurLength  int = 1\n        ,   @ReturnString varchar(8000)=''\n\n    SELECT @Length = len(@InputString)\n\n    -- // Begin looping through each char checking ASCII value\n    -- ===========================================================\n    WHILE (@CurLength &lt;= (@Length+1))\n    BEGIN\n        IF  (ASCII(SUBSTRING(@InputString,@CurLength,1)) between 48 and 57      AND @CleanType in (1,3) )\n        or  (ASCII(SUBSTRING(@InputString,@CurLength,1))    between 65 and 90   AND @CleanType in (1,2) )\n        or  (ASCII(SUBSTRING(@InputString,@CurLength,1))    between 97 and 122  AND @CleanType in (1,2) )\n        or  (ASCII(SUBSTRING(@InputString,@CurLength,1))    = 32    AND @LeaveSpaces = 1 )\n        BEGIN\n            SET @ReturnString = @ReturnString + SUBSTRING(@InputString,@CurLength,1)\n        END\n        SET @CurLength = @CurLength + 1\n    END\n\n    RETURN  @ReturnString\nEND\n</code></pre>\n"}, {'answer_id': 35891879, 'author': 'Tobi Adeyemi', 'author_id': 6039441, 'author_profile': 'https://Stackoverflow.com/users/6039441', 'pm_score': 0, 'selected': False, 'text': "<p>If the mobile could start with a Plus(+) I will use the function like this</p>\n\n<pre><code>CREATE FUNCTION [dbo].[Mobile_NoAlpha](@Mobile VARCHAR(1000)) \nRETURNS VARCHAR(1000) \nAS \nBEGIN\n    DECLARE @StartsWithPlus BIT = 0\n\n    --check if the mobile starts with a plus(+)\n    IF LEFT(@Mobile, 1) = '+'\n    BEGIN\n        SET @StartsWithPlus = 1\n\n        --Take out the plus before using the regex to eliminate invalid characters\n        SET @Mobile = RIGHT(@Mobile, LEN(@Mobile)-1) \n    END\n\n    WHILE PatIndex('%[^0-9]%', @Mobile) &gt; 0 \n        SET @Mobile = Stuff(@Mobile, PatIndex('%[^0-9]%', @Mobile), 1, '')  \n\n    IF @StartsWithPlus = 1\n        SET @Mobile = '+' + @Mobile\n    RETURN @Mobile \nEND\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/52315', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5202/']