Sunday, November 4, 2012

Remove non alphabetic character from string in SQL Server

Query to remove non alphabetic character from string in sql server:


DECLARE @str varchar(100) = 'Paresh@#$Patel#$!@#888'

SELECT CAST(
  (SELECT CASE
           WHEN SUBSTRING(@str, n, 1) LIKE '[A-Z]'
           THEN SUBSTRING(@str, n, 1)
           ELSE ''
          END
  FROM (SELECT number
        FROM master..spt_values
        WHERE type = 'P'
          AND number BETWEEN 1 AND 50) AS Number(n)
  WHERE n <= LEN(@str)
  FOR XML PATH('')) AS NVARCHAR(100))

Run this query you will get the output like :

PareshPatel