Remove special characters and numbers from column

Question

How to remove all special characters and numbers except spaces from specific column in microsoft sql server?

Please define "_special character_". Is Numbers only 0-9 or everything that Unicode classifies as number? — Arminius, Aug 25 '17 at 12:43
If you have to do it within SQL server, have a look at https://stackoverflow.com/questions/21378193/regex-pattern-inside-sql-replace-function but this will be sloowww... I would recommend validating input data and do the cleanup for existing data by exporting, cleaning and re-importing the data for the column. — Arminius, Aug 25 '17 at 12:49
Please refer to https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server — Carlos Alves Jorge, Aug 25 '17 at 12:51

score 0 · Accepted Answer · answered Aug 25 '17 at 13:30

The links above all use loops to solve this. There is no need to resort to loops for this type of thing. Instead we can use a tally table. I keep a tally table as a view in my system like this.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

Now we can leverage that tally as a set. This is a LOT faster than those loop based solutions.

create function GetOnlyCharacters
(
    @SearchVal varchar(8000)
) returns table as return

    with MyValues as
    (
        select substring(@SearchVal, N, 1) as number
            , t.N
        from cteTally t 
        where N <= len(@SearchVal)
            and substring(@SearchVal, N, 1) like '[a-z]'
    )

    select distinct NumValue = STUFF((select number + ''
                from MyValues mv2
                order by mv2.N
                for xml path('')), 1, 0, '')
    from MyValues mv

Remove special characters and numbers from column

1 Answers1