Avoid special symbols in SQL Server

Question

I have a question about SQL Server.

Table Emp:

Id     | Name
-------+-------------------
1      |test“te d'abc
2      |anr¤a
3      | gs-re-C“te d'ab
4      |M‚fe, DF
5      |R™temd
6      |™jad”ji
7      |Cje y ret¢n
8      |J™kl™balu
9      |le“ne-iokd
10     |liode-Pyr‚n‚ie
11     |V„s G”ta
12     |Sƒo Paulo
13     |vAstra gAtaland
14     |¥uble / Bio-Bio
15     |U“pl™n/ds VAsb-y

I need to replace the special characters with empty values.

Based on the above table, I want to output something like below. I tried like this:

select 
    REPLACE(state, SUBSTRING(state, PATINDEX('%[^"a-z"]%', state), 1), '') as name,
    id 
from emp

This query doesn't return the expected result.

Output is:

Id     | Name
-------+-------------
1      |testtedabc
2      |anra
3      |gsreCedab
4      |MfeDF
5      |Rtemd
6      |jadji
7      |Cjeyretn
8      |Jkbalu
9      |eneiokd
10     |iodePyrnie
11     |VsGta
12     |SoPaulo
13     |vAstragAtaand
14     |ubleBioBio
15     |UpndsVAsby

Please tell me how to write a query to achieve this task in SQL Server.

See http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server for a UDF for how to remove non-alphanumeric characters from a string, which is significantly more readable than trying to perform inline replacement (and you are likely to need to do this elsewhere I am assuming in your queries). — Andrew Loree, Mar 18 '16 at 04:13

score 2 · Answer 1 · edited May 23 '17 at 12:22

Here is the link for the same answer on the duplicate question.

You need a pattern-based splitter for this. 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 e.Id, x.Name
FROM emp e
CROSS APPLY(
    SELECT Item + ''
    FROM dbo.PatternSplitCM(e.state, '[a-z]')
    WHERE Matched = 1
    ORDER BY ItemNumber
    FOR XML PATH('')
) x (Name)

SQL Fiddle

Result:

| Id |           Name |
|----|----------------|
|  1 |     testtedabc |
|  2 |           anra |
|  3 |     gsreCtedab |
|  4 |          MfeDF |
|  5 |          Rtemd |
|  6 |          jadji |
|  7 |       Cjeyretn |
|  8 |        Jklbalu |
|  9 |       leneiokd |
| 10 |    liodePyrnie |
| 11 |          VsGta |
| 12 |        SoPaulo |
| 13 | vAstragAtaland |
| 14 |     ubleBioBio |
| 15 |    UplndsVAsby |

Hi its working good.is it possible to get result without using function concept.can you please tell me how to write query achieve this task in sql server. — vasulr, Mar 18 '16 at 05:13
This is a good answer. Can you post this answer here [how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server](https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server) — Pரதீப், Mar 18 '16 at 06:23

Avoid special symbols in SQL Server

1 Answers1