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

Question

How could you remove all characters that are not alphabetic from a string    What about non-alphanumeric   Does this have to be a custom function or are there also more generalizable solutions

User · Answer

I just found this built into Oracle 10g if that is what you re using  I had to strip all the special characters out for a phone number compare   regexp replace c phone     0-9

User · Answer

From performance perspective I d use Inline Function   SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO CREATE FUNCTION  dbo   udf RemoveNumericCharsFromString     List NVARCHAR 4000    RETURNS TABLE  AS RETURN      WITH GetNums AS          SELECT TOP ISNULL DATALENGTH  List   0           n   ROW NUMBER   OVER ORDER BY  SELECT NULL           FROM            VALUES  0   0   0   0   d  n              VALUES  0   0   0   0   0   0   0   0   0   0   e  n              VALUES  0   0   0   0   0   0   0   0   0   0   f  n              VALUES  0   0   0   0   0   0   0   0   0   0   g  n                     SELECT StrOut                SELECT Chr          FROM GetNums             CROSS APPLY  SELECT SUBSTRING  List   n 1   X Chr           WHERE Chr LIKE     0-9              ORDER BY N          FOR XML PATH      TYPE  value      NVARCHAR MAX           How to Use    SELECT StrOut FROM dbo udf RemoveNumericCharsFromString   vv45--9gut      Result  vv--gut

User · Answer

Having looked at all the given solutions I thought that there has to be a pure SQL method that does not require a function or a CTE   XML query  and doesn t involve difficult to maintain nested REPLACE statements  Here is my solution   SELECT    x    CASE WHEN a NOT LIKE       SUBSTRING x  1  1        THEN    ELSE SUBSTRING x  1  1  END       CASE WHEN a NOT LIKE       SUBSTRING x  2  1        THEN    ELSE SUBSTRING x  2  1  END       CASE WHEN a NOT LIKE       SUBSTRING x  3  1        THEN    ELSE SUBSTRING x  3  1  END       CASE WHEN a NOT LIKE       SUBSTRING x  4  1        THEN    ELSE SUBSTRING x  4  1  END       CASE WHEN a NOT LIKE       SUBSTRING x  5  1        THEN    ELSE SUBSTRING x  5  1  END       CASE WHEN a NOT LIKE       SUBSTRING x  6  1        THEN    ELSE SUBSTRING x  6  1  END -- Keep adding rows until you reach the column size      AS stripped column FROM  SELECT          column to strip AS x           ABCDEFGHIJKLMNOPQRSTUVWXYZ  AS a        FROM my table  a   The advantage of doing it this way is that the valid characters are contained in the one string in the sub query making easy to reconfigure for a different set of characters   The downside is that you have to add a row of SQL for each character up to the size of your column  To make that task easier I just used the Powershell script below  this example if for a VARCHAR 64    1  64                CASE WHEN a NOT LIKE       SUBSTRING x   0   1        THEN    ELSE SUBSTRING x   0   1  END  -f        clip exe

User · Answer

this way didn t work for me as i was trying to keep the Arabic letters i tried to replace the regular expression but also it didn t work  i wrote another method to work on ASCII level as it was my only choice and it worked    Create function  dbo   RemoveNonAlphaCharacters    s varchar 4000   returns varchar 4000     with schemabinding begin    if  s is null       return null    declare  s2 varchar 4000     set  s2         declare  l int    set  l   len  s     declare  p int    set  p   1    while  p  lt    l begin       declare  c int       set  c   ascii substring  s   p  1         if  c between 48 and 57 or  c between 65 and 90 or  c between 97 and 122 or  c between 165 and 253 or  c between 32 and 33          set  s2    s2   char  c        set  p    p   1       end    if len  s2    0       return null    return  s2    end   GO

User · Answer

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   gt  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   Hope it helps

User · Answer

I put this in both places where PatIndex is called   PatIndex     A-Za-z0-9      Temp    for the custom function above RemoveNonAlphaCharacters and renamed it RemoveNonAlphaNumericCharacters

User · Answer

--First create one function  CREATE FUNCTION  dbo   GetNumericonly    strAlphaNumeric VARCHAR 256   RETURNS VARCHAR 256  AS BEGIN      DECLARE  intAlpha INT      SET  intAlpha   PATINDEX     0-9      strAlphaNumeric  BEGIN      WHILE  intAlpha  gt  0    BEGIN           SET  strAlphaNumeric   STUFF  strAlphaNumeric   intAlpha  1                 SET  intAlpha   PATINDEX     0-9      strAlphaNumeric      END END RETURN ISNULL  strAlphaNumeric 0  END   Now call this function like  select  dbo   GetNumericonly   Abhi12shek23jaiswal     Its result like  1223

User · Answer

Here is another way to remove non-alphabetic characters using an iTVF  First  you need a pattern-based string splitter  Here is one taken from Dwain Camp s article   -- PatternSplitCM will split a string based on a pattern of the form  -- supported by LIKE and PATINDEX  --  -- Created by  Chris Morris 12-Oct-2012  CREATE FUNCTION  dbo   PatternSplitCM            List                VARCHAR 8000    NULL          Pattern            VARCHAR 50    RETURNS TABLE WITH SCHEMABINDING  AS   RETURN     WITH numbers AS           SELECT TOP ISNULL DATALENGTH  List   0               n   ROW NUMBER   OVER ORDER BY  SELECT NULL           FROM          VALUES  0   0   0   0   0   0   0   0   0   0   d  n            VALUES  0   0   0   0   0   0   0   0   0   0   e  n            VALUES  0   0   0   0   0   0   0   0   0   0   f  n            VALUES  0   0   0   0   0   0   0   0   0   0   g  n             SELECT         ItemNumber   ROW NUMBER   OVER ORDER BY MIN n            Item   SUBSTRING  List MIN n  1 MAX n -MIN n             Matched      FROM           SELECT n  y  Matched   Grouper   n - ROW NUMBER   OVER ORDER BY y  Matched  n          FROM numbers         CROSS APPLY               SELECT  Matched    CASE WHEN SUBSTRING  List n 1  LIKE  Pattern THEN 1 ELSE 0 END           y       d     GROUP BY  Matched   Grouper   Now that you have a pattern-based splitter  you need to split the strings that match the pattern    a-z    and then concatenate them back to get the desired result   SELECT   FROM tbl t CROSS APPLY      SELECT Item          FROM dbo PatternSplitCM t str    a-z        WHERE Matched   1     ORDER BY ItemNumber     FOR XML PATH       x  a      SAMPLE  Result     Id                str                a    ---- ------------------ ----------------     1      test   te d abc       testtedabc      2              anr  a             anra      3    gs-re-C   te d ab       gsreCtedab      4           M   fe  DF            MfeDF      5             R   temd            Rtemd      6               jad   ji            jadji      7        Cje y ret  n         Cjeyretn      8          J   kl   balu          Jklbalu      9         le   ne-iokd         leneiokd     10     liode-Pyr   n   ie      liodePyrnie     11           V   s G   ta            VsGta     12          S  o Paulo          SoPaulo     13    vAstra gAtaland   vAstragAtaland     14      uble   Bio-Bio       ubleBioBio     15   U   pl   n ds VAsb-y      UplndsVAsby

User · Answer

Though post is a bit old  I would like to say the following  Issue I had with above solution is that it does not filter out characters like             etc  I adapted a function as follows  I only used an 80 varchar string to save memory    create FUNCTION dbo udf Cleanchars   InputString varchar 80    RETURNS varchar 80   AS   BEGIN  declare  return varchar 80     length int    counter int    cur char char 1   SET  return       SET  length   0  SET  counter   1  SET  length   LEN  InputString   IF  length  gt  0  BEGIN WHILE  counter  lt    length   BEGIN SET  cur char   SUBSTRING  InputString   counter  1  IF   ascii  cur char  in  32 44 46   or  ascii  cur char  between 48 and 57  or  ascii  cur char  between 65 and 90  or  ascii  cur char  between 97 and 122   BEGIN SET  return    return    cur char END  SET  counter    counter   1  END END   RETURN  return END

User · Answer

This solution  inspired by Mr  Allen s solution  requires a Numbers table of integers  which you should have on hand if you want to do serious query operations with good performance   It does not require a CTE  You can change the NOT IN       expression to exclude specific characters  or change it to an IN       OR LIKE expression to retain only certain characters   SELECT       SELECT  SUBSTRING  YourString   N  1      FROM    dbo Numbers     WHERE   N  gt  0 AND N  lt   CONVERT INT  LEN  YourString            AND SUBSTRING  YourString   N  1  NOT IN                       FOR XML PATH       AS  YourStringTransformed  FROM

User · Answer

Another possibe option for SQL Server 2017   without loops and or recursion  is a string-based approach using TRANSLATE   and REPLACE    T-SQL statement  DECLARE  pattern varchar 52     abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ   SELECT     v  Text       REPLACE        TRANSLATE           v  Text            REPLACE TRANSLATE v  Text    pattern  REPLICATE  a   LEN  pattern      a                 REPLICATE  0   LEN REPLACE TRANSLATE v  Text    pattern  REPLICATE  a   LEN  pattern      a                         0                 AS AlphabeticCharacters FROM  VALUES      abc1234def5678ghi90jkl    amp          1234567890         JAHDBESBN        E sd55bn     v   Text    or as a function  CREATE FUNCTION dbo RemoveNonAlphabeticCharacters   Text varchar 1000    RETURNS varchar 1000  AS BEGIN     DECLARE  pattern varchar 52     abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ     SET  text   REPLACE        TRANSLATE            Text           REPLACE TRANSLATE  Text   pattern  REPLICATE  a   LEN  pattern      a                 REPLICATE  0   LEN REPLACE TRANSLATE  Text   pattern  REPLICATE  a   LEN  pattern      a                         0                        RETURN  Text END

User · Answer

Parameterized version of G Mastros  awesome answer   CREATE FUNCTION  dbo   fn StripCharacters         String NVARCHAR MAX         MatchExpression VARCHAR 255    RETURNS NVARCHAR MAX  AS BEGIN     SET  MatchExpression          MatchExpression           WHILE PatIndex  MatchExpression   String   gt  0         SET  String   Stuff  String  PatIndex  MatchExpression   String   1           RETURN  String  END   Alphabetic only   SELECT dbo fn StripCharacters  a1 s2 d3 f4      a-z     Numeric only   SELECT dbo fn StripCharacters  a1 s2 d3 f4      0-9     Alphanumeric only   SELECT dbo fn StripCharacters  a1 s2 d3 f4      a-z0-9     Non-alphanumeric   SELECT dbo fn StripCharacters  a1 s2 d3 f4     a-z0-9

User · Answer

Here s another recursive CTE solution  based on  Gerhard Weiss s answer here   You should be able to copy and paste the whole code block into SSMS and play with it there   The results include a few extra columns to help us understand what s going on   It took me a while until I understood all that s going on with both PATINDEX  RegEx  and the recursive CTE   DECLARE  DefineBadCharPattern varchar 30  SET  DefineBadCharPattern       A-z     --Means anything NOT between A and z characters  according to ascii char value  is  bad  SET  DefineBadCharPattern       a-z0-9     --Means anything NOT between a and z characters or numbers 0 through 9  according to ascii char value  are  bad  SET  DefineBadCharPattern        -      --Means anything NOT between space and   characters  all non-printable characters  is  bad  --Change  ReplaceBadCharWith to    to strip  bad  characters from string --Change to some character if you want to  see  what s being replaced  NOTE  It must be allowed accoring to  DefineBadCharPattern above DECLARE  ReplaceBadCharWith varchar 1         --Change this to whatever you want to replace non-printable chars with  IF patindex  DefineBadCharPattern COLLATE Latin1 General BIN   ReplaceBadCharWith   gt  0     BEGIN         RAISERROR   ReplaceBadCharWith value   s  must be a character allowed by PATINDEX pattern of  s  16 1  ReplaceBadCharWith   DefineBadCharPattern          RETURN     END --A table of values to play with  DECLARE  temp TABLE  OriginalString varchar 100   INSERT  temp SELECT   1hello    char 13    char 10     there    char 30    char 9    char 13    char 10  INSERT  temp SELECT  2hello    char 30     there    char 30  INSERT  temp SELECT   3hello there  INSERT  temp SELECT   tab    char 9      character  INSERT  temp SELECT  good bye   --Let the magic begin   WITH recurse AS       select     OriginalString      OriginalString as CleanString      patindex  DefineBadCharPattern COLLATE Latin1 General BIN  OriginalString  as  Position       substring OriginalString patindex  DefineBadCharPattern COLLATE Latin1 General BIN  OriginalString  1  as  InvalidCharacter       ascii substring OriginalString patindex  DefineBadCharPattern COLLATE Latin1 General BIN  OriginalString  1   as  ASCIICode      from  temp    UNION ALL     select     OriginalString      CONVERT varchar 100  REPLACE CleanString InvalidCharacter  ReplaceBadCharWith        patindex  DefineBadCharPattern COLLATE Latin1 General BIN CleanString  as  Position       substring CleanString patindex  DefineBadCharPattern COLLATE Latin1 General BIN CleanString  1       ascii substring CleanString patindex  DefineBadCharPattern COLLATE Latin1 General BIN CleanString  1       from recurse     where patindex  DefineBadCharPattern COLLATE Latin1 General BIN CleanString   gt  0   SELECT   FROM recurse --optionally comment out this last WHERE clause to see more of what the recursion is doing  WHERE patindex  DefineBadCharPattern COLLATE Latin1 General BIN CleanString    0

User · Answer

DECLARE  vchVAlue NVARCHAR 255     SWP  Lettering Position 1  4    2  8    3  16    4     5     6     Voltage Selector  Solder  6  Step switch    w o fuseholder     WHILE PATINDEX         CAST  vchVAlue AS VARCHAR 255     gt  0   BEGIN     SELECT  vchVAlue   STUFF  vchVAlue PATINDEX         CAST  vchVAlue AS VARCHAR 255    1        END   SELECT  vchVAlue

User · Answer

Believe it or not  in my system this ugly function performs better than G Mastros elegant one   CREATE FUNCTION dbo RemoveSpecialChar   s VARCHAR 256    RETURNS VARCHAR 256   WITH SCHEMABINDING     BEGIN         IF  s IS NULL             RETURN NULL         DECLARE  s2 VARCHAR 256                         l INT   LEN  s                    p INT   1          WHILE  p  lt    l             BEGIN                 DECLARE  c INT                 SET  c   ASCII SUBSTRING  s   p  1                   IF  c BETWEEN 48 AND 57                    OR   c BETWEEN 65 AND 90                    OR   c BETWEEN 97 AND 122                     SET  s2    s2   CHAR  c                  SET  p    p   1             END          IF LEN  s2    0             RETURN NULL          RETURN  s2

User · Answer

If you are like me and don t have access to just add functions to your production data but still want to perform this kind of filtering  here s a pure SQL solution using a PIVOT table to put the filtered pieces back together again    N B  I hardcoded the table up to 40 characters  you ll have to add more if you have longer strings to filter   SET CONCAT NULL YIELDS NULL OFF   with      ToBeScrubbed as       select 1 as id    SOME 222         BOGUS     amp   DATA  as ColumnToScrub     Scrubbed as       select          P Number as ValueOrder          isnull   substring   t ColumnToScrub   number   1          as ScrubbedValue          t id     from         ToBeScrubbed t         left join master  spt values P             on P number between 1 and len t ColumnToScrub              and type   P      where         PatIndex     a-z     substring t ColumnToScrub P number 1      0    SELECT     id        1    2    3    4    5    6    7    8    9    10          11    12    13    14    15    16    17    18    19    20          21    22    23    24    25    26    27    28    29    30          31    32    33    34    35    36    37    38    39    40  as ScrubbedData FROM       select                from          Scrubbed            src     PIVOT           MAX ScrubbedValue  FOR ValueOrder IN            1    2    3    4    5    6    7    8    9    10            11    12    13    14    15    16    17    18    19    20            21    22    23    24    25    26    27    28    29    30            31    32    33    34    35    36    37    38    39    40                  pvt

User · Answer

I knew that SQL was bad at string manipulation  but I didn t think it would be this difficult  Here s a simple function to strip out all the numbers from a string  There would be better ways to do this  but this is a start   CREATE FUNCTION dbo AlphaOnly        String varchar 100    RETURNS varchar 100  AS BEGIN   RETURN       REPLACE        REPLACE          REPLACE            REPLACE              REPLACE                REPLACE                  REPLACE                    REPLACE                      REPLACE                        REPLACE                           String                         9                             8                           7                         6                       5                     4                   3                 2               1             0           END GO  --                    DECLARE  t TABLE       ColID       int      ColString   varchar 50     INSERT INTO  t VALUES  1   abc1234567890    SELECT ColID  ColString  dbo AlphaOnly ColString  FROM  t   Output  ColID ColString ----- ------------- ---     1 abc1234567890 abc   Round 2 - Data-Driven Blacklist  --                                              -- Create a table of blacklist characters --                                              IF EXISTS  SELECT   FROM sys tables WHERE  object id    OBJECT ID  dbo CharacterBlacklist      DROP TABLE dbo CharacterBlacklist GO CREATE TABLE dbo CharacterBlacklist       CharID              int         IDENTITY      DisallowedCharacter nchar 1     NOT NULL   GO INSERT INTO dbo CharacterBlacklist  DisallowedCharacter  VALUES  N 0   INSERT INTO dbo CharacterBlacklist  DisallowedCharacter  VALUES  N 1   INSERT INTO dbo CharacterBlacklist  DisallowedCharacter  VALUES  N 2   INSERT INTO dbo CharacterBlacklist  DisallowedCharacter  VALUES  N 3   INSERT INTO dbo CharacterBlacklist  DisallowedCharacter  VALUES  N 4   INSERT INTO dbo CharacterBlacklist  DisallowedCharacter  VALUES  N 5   INSERT INTO dbo CharacterBlacklist  DisallowedCharacter  VALUES  N 6   INSERT INTO dbo CharacterBlacklist  DisallowedCharacter  VALUES  N 7   INSERT INTO dbo CharacterBlacklist  DisallowedCharacter  VALUES  N 8   INSERT INTO dbo CharacterBlacklist  DisallowedCharacter  VALUES  N 9   GO  --                                      IF EXISTS  SELECT   FROM sys objects WHERE  object id    OBJECT ID  dbo StripBlacklistCharacters      DROP FUNCTION dbo StripBlacklistCharacters GO CREATE FUNCTION dbo StripBlacklistCharacters        String nvarchar 100    RETURNS varchar 100  AS BEGIN   DECLARE  blacklistCt  int   DECLARE  ct           int   DECLARE  c            nchar 1     SELECT  blacklistCt   COUNT    FROM dbo CharacterBlacklist    SET  ct   0   WHILE  ct  lt   blacklistCt BEGIN     SET  ct    ct   1      SELECT  String   REPLACE  String  DisallowedCharacter  N        FROM dbo CharacterBlacklist     WHERE CharID    ct   END    RETURN   String  END GO  --                                      DECLARE  s  nvarchar 24  SET  s   N abc1234def5678ghi90jkl   SELECT      s                  AS OriginalString      dbo StripBlacklistCharacters  s    AS ResultString   Output  OriginalString           ResultString ------------------------ ------------ abc1234def5678ghi90jkl   abcdefghijkl   My challenge to readers  Can you make this more efficient  What about using recursion

User · Answer

Here s a solution that doesn t require creating a function or listing all instances of characters to replace  It uses a recursive WITH statement in combination with a PATINDEX to find unwanted chars  It will replace all unwanted chars in a column - up to 100 unique bad characters contained in any given string   E G   ABC123DEF234  would contain 4 bad characters 1  2  3 and 4  The 100 limit is the maximum number of recursions allowed in a WITH statement  but this doesn t impose a limit on the number of rows to process  which is only limited by the memory available  If you don t want DISTINCT results  you can remove the two options from the code   -- Create some test data  SELECT   INTO  testData  FROM  VALUES   ABC DEF K l p      123H J 234     ABCD EFG    as t TXT   -- Actual query  -- Remove non-alpha chars      A-Z    -- Remove non-alphanumeric chars      A-Z0-9    DECLARE  BadCharacterPattern VARCHAR 250        A-Z      WITH recurMain as       SELECT DISTINCT CAST TXT AS VARCHAR 250   AS TXT  PATINDEX  BadCharacterPattern  TXT  AS BadCharIndex     FROM  testData     UNION ALL     SELECT CAST TXT AS VARCHAR 250   AS TXT  PATINDEX  BadCharacterPattern  TXT  AS BadCharIndex     FROM           SELECT              CASE WHEN BadCharIndex  gt  0                  THEN REPLACE TXT  SUBSTRING TXT  BadCharIndex  1                       ELSE TXT              END AS TXT         FROM recurMain         WHERE BadCharIndex  gt  0       badCharFinder   SELECT DISTINCT TXT FROM recurMain WHERE BadCharIndex   0

User · Answer

Using a CTE generated numbers table to examine each character  then FOR XML to concat to a string of kept values you can     CREATE FUNCTION  dbo   PatRemove        pattern varchar 50        expression varchar 8000         RETURNS varchar 8000  AS BEGIN     WITH          d d  AS  SELECT d FROM  VALUES  0   1   2   3   4   5   6   7   8   9   digits d            nums n  AS  SELECT ROW NUMBER   OVER  ORDER BY  SELECT NULL   FROM d d1  d d2  d d3  d d4           chars c  AS  SELECT SUBSTRING  expression  n  1  FROM nums WHERE n  lt   LEN  expression       SELECT           expression    SELECT c AS  text    FROM chars WHERE c NOT LIKE  pattern FOR XML PATH            RETURN  expression  END

[sql-server] How to strip all non-alphabetic characters from string in SQL Server?

Examples related to sql-server

Examples related to parsing

Examples related to user-defined-functions

Examples related to alphanumeric

Examples related to alphabetic