How to strip all non-alphabetic characters from string in SQL Server?

by

Try this function:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues="%[^a-z]%"
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Call it like this:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

Once you understand the code, you should see that it is relatively simple to change it to remove other characters, too. You could even make this dynamic enough to pass in your search pattern.

More Related Contents:

  1. Efficient ISNUMERIC() replacements on SQL Server?
  2. SQL to find first non-numeric character in a string
  3. how to write number to word function in sql server [closed]
  4. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  5. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  6. Does query plan optimizer works well with joined/filtered table-valued functions?
  7. SQL Server – error when creating stored procedure
  8. How can I escape square brackets in a LIKE clause?
  9. SQL Server, How to set auto increment after creating a table without data loss?
  10. How can I truncate a datetime in SQL Server?
  11. Datatype for storing ip address in SQL Server
  12. SQL Server: Query fast, but slow from procedure
  13. Errors: “INSERT EXEC statement cannot be nested.” and “Cannot use the ROLLBACK statement within an INSERT-EXEC statement.” How to solve this?
  14. Is there a way to loop through a table variable in TSQL without using a cursor?
  15. sql server invalid object name – but tables are listed in SSMS tables list
  16. What columns generally make good indexes?
  17. What is a free tool to compare two SQL Server Databases? [closed]
  18. How can I make HTTP request from SQL server?
  19. SQL Server Management Studio alternatives to browse/edit tables and run queries [closed]
  20. Why is a UDF so much slower than a subquery?
  21. Format SQL in SQL Server Management Studio
  22. SQL Server, the misleading XLOCK & optimizations
  23. #DELETE viewing SQL Server table in Access
  24. Grant Select on a view not base table when base table is in a different database
  25. SQL Server (localdb)\v11.0 explained
  26. SQL Server – join rows into comma separated list
  27. Generate table relationship diagram from existing schema (SQL Server) [closed]
  28. Bootstrapping SQL Express from WiX?
  29. Error converting data types when importing from Excel to SQL Server 2008
  30. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?

Leave a Comment