CODEWITHSUNDEEP
sql-serverparsinguser-defined-functionsalphanumericalphabetic
Even Mien
Even Mien

Reputation: 45868

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

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?

Upvotes: 225

Views: 533179

Answers (23)

David Wiltcher
David Wiltcher

Reputation: 1

I know this is a very old post but just wanted to add this in case anyone was interested. Two simple solutions for SQL 2017 and above and pre SQL 2017

/* SQL 2017 and above */
declare @str varchar(50) = 'dff dfdff   !"£$%^&*()_-+=@~#?/>.<   dfd dfd'

;WITH nums AS
(
  SELECT 1 AS Num
  UNION ALL
  SELECT num + 1 AS Num
  FROM nums
  WHERE Num + 1 <= LEN(@str)
)
  SELECT STRING_AGG(Val,'')
  FROM (
  SELECT *,SUBSTRING(@str,Num,1) AS val
  FROM nums) a
  WHERE val LIKE '%[A-Z]%'


/*Pre SQL 2017 */
  declare @str varchar(50) = 'dff dfdff   !"£$%^&*()_-+=@~#?/>.<   dfd dfd'

;WITH nums AS
(
  SELECT 1 AS Num
  UNION ALL
  SELECT num + 1 AS Num
  FROM nums
  WHERE Num + 1 <= LEN(@str)
)
  SELECT STUFF((SELECT '' +  Val AS [text()]
  FROM (
  SELECT *,SUBSTRING(@str,Num,1) AS val
  FROM nums) a
  WHERE val LIKE '%[A-Z]%'
  FOR XML PATH ('')),1,0,'')

Upvotes: 0

Victor Nazarov
Victor Nazarov

Reputation: 126

CREATE FUNCTION [dbo].[ReplaceNotAllowedCharacters](@data VARCHAR(MAX), @allowed VARCHAR(1000) = '!"#$%&\''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\\]^_`abcdefghijklmnopqrstuvwxyz{|}~', @replace VARCHAR(1) = '', @joinReplace BIT = 0)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @search VARCHAR(1000) = CONCAT('%[^', REPLACE(CONCAT(@allowed, @replace), ']', CHAR(0)) ,']%')
    DECLARE @idx AS INT = PATINDEX(@search, @data)

    SET @data = REPLACE(REPLACE(@data, CHAR(0), ''), ']', CHAR(0))

    WHILE @idx > 0
        SELECT @data = REPLACE(@data, SUBSTRING(@data, @idx, 1), @replace),
               @idx = PATINDEX(@search, @data)
    
    SET @idx = 0
    WHILE @joinReplace = 1 AND @idx != LEN(@data)
        SELECT @idx = LEN(@data),
               @data = REPLACE(@data, CONCAT(@replace, @replace), @replace)

    RETURN REPLACE(@data, CHAR(0), ']')
END
GO

SELECT [dbo].[ReplaceNotAllowedCharacters]('Te’st ŀ  String', DEFAULT, DEFAULT, DEFAULT)
> TestString

SELECT [dbo].[ReplaceNotAllowedCharacters]('Test   String', 'A-Z', '_', 1)
> Test_String

SELECT [dbo].[ReplaceNotAllowedCharacters]('T  e s t   S t r  i n g', 'A-Z', '_', 1)
> T_e_s_t_S_t_r_i_n_g

Upvotes: 0

J Brun
J Brun

Reputation: 1284

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 <= @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

Upvotes: 13

Zhorov
Zhorov

Reputation: 29943

Original 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#@$&'),
   ('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

Update

Thanks to the @ttugates's comment, I've found a small issue in this approach. The actual edge case is the 'V 0 S' as an input. The LEN() function truncates trailing spaces, which results in "The second and third arguments of the TRANSLATE built-in function must contain an equal number of characters" error message. A solution is a small change in the length calculation, but the approach remains the same:

  • All alphabetic characters (or characters from the given pattern) in the input text are translated to 'a' and after that replaced with ''.
  • The remaining text after the first step includes only non-alphabetic characters (or characters not in the given pattern).
  • All non-alphabetic characters in the input text are translated to '0' and after that replaced with ''.
  • The remaining text is the actual result.
  • The new length calculation is LEN(REPLACE(@text, ' ', '.')).

Updated function:

CREATE FUNCTION dbo.RemoveNonAlphabeticCharacters (@Text varchar(1000)) 
RETURNS varchar(1000)
AS BEGIN

   DECLARE @pattern varchar(52) = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
   DECLARE @translations varchar(1000)

   SET @translations = TRANSLATE(@Text, @pattern, REPLICATE('a', LEN(@pattern))) 
   SET @translations = REPLACE(@translations, 'a', '')

   SET @Text = TRANSLATE(@Text, @translations, REPLICATE('0', LEN(REPLACE(@translations, ' ', '.'))))
   SET @Text = REPLACE(@Text, '0', '')
   
   RETURN @Text

END

Upvotes: 7

Andre Ferreira
Andre Ferreira

Reputation: 1

CREATE FUNCTION remove_spc_char(@str VARCHAR(MAX))
  RETURNS VARCHAR(MAX) 
AS
BEGIN
  DECLARE @resp    VARCHAR(MAX) = '';
  DECLARE @str_val   VARCHAR(MAX) = UPPER(@str);
  DECLARE @i       INTEGER= 1;
  DECLARE @v_asc   INTEGER;
   WHILE @i <= (LEN(@str_val))
   BEGIN
     SET @v_asc = (ASCII(SUBSTRING(@str_val, @i, 1))) 
        BEGIN
        IF @v_asc in (192,193,194,195,196,65) 
            begin
                SET @v_asc = 65;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (200,201,202,203,233,69)
            begin
                SET @v_asc = 69;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (204,205,206,207,296,73)
            begin
                SET @v_asc = 73;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (210,211,212,213,214,79)
            begin
                SET @v_asc = 79;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (217,218,219,220,85)
            begin
                SET @v_asc = 85;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (199,231,67)
            begin
                SET @v_asc = 67;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (209,78)
            begin
                SET @v_asc = 78;
                SET @resp = concat(@resp, CHAR(@v_asc));
            end;
        IF @v_asc in (924,181,358,216,222,330,272,208,198,42,37,38,34,36,35,
64,33,39,41,40,43,61,95,45,62,60,63,47,176,183,124,166,174,359,248,254,
180,170,186,126,312,331,273,172,178,179,163,162,123,91,93,125,92,167,240,
223,230,171,187,169,185,168)
            begin
                SET @resp = concat(@resp, '');
            end;
        ELSE 
            begin
                if @v_asc not in (65,67,69,73,78,79,85)
                begin
                    SET @resp = concat(@resp, CHAR(@v_asc));
                end;
            end;
        END;
      SET @i = @i + 1
    END;
    RETURN @resp;
END;

Upvotes: 0

George Mastros
George Mastros

Reputation: 24498

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.

Upvotes: 438

Even Mien
Even Mien

Reputation: 45868

Parameterized version of George 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) > 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')

Upvotes: 219

MarianoC
MarianoC

Reputation: 21

For SQL Server >= 2017...

declare @text varchar(max)

-- create some sample text
select
@text=
'
Lorem @ipsum  *&dolor-= sit?! amet, {consectetur } adipiscing\ elit. Vivamus commodo justo metus, sed facilisis ante 
congue eget. Proin ac bibendum sem/.
'

-- the characters to be removed
declare @unwanted varchar(max)='''.,!?/<>"[]{}|`~@#$%^&*()-+=/\:;'+char(13)+char(10)

-- interim replaced with
declare @replace_with char(1)=' '

-- call the translate function that will change unwanted characters to spaces
-- in this sample
declare @translated varchar(max)
select @translated=TRANSLATE(@text,@unwanted,REPLICATE(@replace_with,len(@unwanted)))

-- In this case, I want to preserve one space
select  string_agg(trim(value),' ')
from    STRING_SPLIT(@translated,' ')
where   trim(value)<>''

-- Result
'Lorem ipsum dolor sit amet consectetur adipiscing elit Vivamus commodo justo metus sed facilisis ante congue eget Proin ac bibendum sem'

Upvotes: 2

Baodad
Baodad

Reputation: 2491

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) > 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) > 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

Upvotes: 2

hkravitz
hkravitz

Reputation: 1385

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
   */

Upvotes: 2

AGM
AGM

Reputation: 21

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 <= @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

Upvotes: 0

Mohannad Qattash
Mohannad Qattash

Reputation: 1

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))) > 0
  BEGIN
    SELECT @vchVAlue = STUFF(@vchVAlue,PATINDEX('%?%' , CAST(@vchVAlue AS VARCHAR(255))),1,' ')
  END 

SELECT @vchVAlue

Upvotes: 0

Eric
Eric

Reputation: 9

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 > 0 
BEGIN WHILE @counter <= @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

Upvotes: -1

Abhishek Jaiswal
Abhishek Jaiswal

Reputation: 1249

--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 > 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

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

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 |

Upvotes: 3

Graeme Job
Graeme Job

Reputation: 1149

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 > 0 
                THEN REPLACE(TXT, SUBSTRING(TXT, BadCharIndex, 1), '')
                ELSE TXT 
            END AS TXT
        FROM recurMain
        WHERE BadCharIndex > 0
    ) badCharFinder
)
SELECT DISTINCT TXT
FROM recurMain
WHERE BadCharIndex = 0;

Upvotes: 6

Dave Sexton
Dave Sexton

Reputation: 11188

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

Upvotes: 5

Jay Bienvenu
Jay Bienvenu

Reputation: 3293

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 > 0 AND N <= CONVERT(INT, LEN([YourString]))
        AND SUBSTRING([YourString], N, 1) NOT IN ('(',')',',','.')
    FOR XML PATH('')
) AS [YourStringTransformed]
FROM ...

Upvotes: 2

Dennis Allen
Dennis Allen

Reputation: 412

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 <= LEN(@expression))
    SELECT 
        @expression = (SELECT c AS [text()] FROM chars WHERE c NOT LIKE @pattern FOR XML PATH(''));

    RETURN @expression;
END

Upvotes: 0

Lu_Bu
Lu_Bu

Reputation: 119

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]', '')

Upvotes: -3

Kyle Hale
Kyle Hale

Reputation: 8120

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 !@*&! 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

Upvotes: 4

user381460
user381460

Reputation: 11

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

Upvotes: 1

Rob Garrison
Rob Garrison

Reputation: 7234

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 < @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?

Upvotes: 7

Related Questions