T-SQL strip all non-alpha and non-numeric characters

by

One flexible-ish way;

CREATE FUNCTION [dbo].[fnRemovePatternFromString](@BUFFER VARCHAR(MAX), @PATTERN VARCHAR(128)) RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @POS INT = PATINDEX(@PATTERN, @BUFFER)
    WHILE @POS > 0 BEGIN
        SET @BUFFER = STUFF(@BUFFER, @POS, 1, '')
        SET @POS = PATINDEX(@PATTERN, @BUFFER)
    END
    RETURN @BUFFER
END

select dbo.fnRemovePatternFromString('cake & beer $3.99!?c', '%[$&.!?]%')

(No column name)
cake  beer 399c

More Related Contents:

  1. T-SQL: Export to new Excel file
  2. SQL MERGE statement to update data
  3. How to get the last record per group in SQL
  4. T-SQL substring – separating first and last name
  5. How to get week number of the month from the date in sql server 2008
  6. Avoiding IF ELSE due to variable been NULL
  7. Recursive Child/Parent queries in T/SQL
  8. I dont know sql syntax for my table [closed]
  9. CROSS JOIN vs INNER JOIN in SQL
  10. Retrieving last record in each group from database – SQL Server 2005/2008
  11. How to generate a range of numbers between two numbers?
  12. Multiple INSERT statements vs. single INSERT with multiple VALUES
  13. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  14. Common Table Expression, why semicolon?
  15. T-SQL split string based on delimiter
  16. INSERT INTO vs SELECT INTO
  17. SQL Recursive CTE: Finding objects linked by property
  18. Why is SQL Server 2008 Management Studio Intellisense not working?
  19. Reporting Services permissions on SQL Server R2 SSRS
  20. Convert decimal time to hours and minutes
  21. Do clustered indexes have to be unique?
  22. How do I prevent a database trigger from recursing?
  23. How to implement polymorphic associations in an existing database
  24. TSQL – How to use GO inside of a BEGIN .. END block?
  25. What is the default transaction isolation level for SQL Server with ADO.NET?
  26. How to combine results of two queries into a single dataset
  27. Executing SQL Server Agent Job from a stored procedure and returning job result
  28. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  29. COALESCE Function in TSQL
  30. Stored procedure hangs seemingly without explanation

Leave a Comment