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

by

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

More Related Contents:

  1. Max sal to print base on manager_id
  2. Creating stored procedure getting Incorrect syntax near the keyword ‘Declare’
  3. Can I use CASE statement in a JOIN condition?
  4. sql server 2008 management studio not checking the syntax of my query
  5. Conversion failed when converting date and/or time from character string while inserting datetime
  6. Create a date from day month and year with T-SQL
  7. How should I pass a table name into a stored proc?
  8. Get the last inserted row ID (with SQL statement) [duplicate]
  9. Get the week start date and week end date from week number
  10. Parse comma-separated string to make IN List of strings in the Where clause
  11. conditional unique constraint
  12. Run all SQL files in a directory
  13. SELECT FOR UPDATE with SQL Server
  14. How to group by month from Date field using sql
  15. unresolved reference to object [INFORMATION_SCHEMA].[TABLES]
  16. Why is my t-sql left join not working?
  17. How to get the numeric part from a string using T-SQL?
  18. SQL Server Update Trigger, Get Only modified fields
  19. Grouped string aggregation / LISTAGG for SQL Server
  20. Curly braces in T-SQL
  21. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  22. How to find column names for all tables in all databases in SQL Server
  23. How do I add a auto_increment primary key in SQL Server database?
  24. SQL Row_Number() function in Where Clause
  25. SQL update trigger only when column is modified
  26. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  27. Try_Convert for SQL Server 2008 R2
  28. SQL Server variable scope in a stored procedure
  29. SQL SERVER DATETIME FORMAT
  30. Insert all values of a table into another table in SQL

Leave a Comment