remove special characters in sql server

Question

This post is hidden. It was deleted 3 hours ago by marc_s, Stu, Dale K.
Closed. This question needs details or clarity. It is not currently accepting answers.
Add details and clarify the problem you’re solving. This will help others answer the question. You can edit the question or post a new one.

Closed 3 hours ago.

How can I remove special characters in SQL Server?

CREATE TABLE [dbo].[emp]
(
    [id] [int] NULL,
    [name] [varchar](50) NULL
) 

INSERT [dbo].[emp] ([id], [name]) VALUES (1, N'?veani')
INSERT [dbo].[emp] ([id], [name]) VALUES (2, N'ravi?')
INSERT [dbo].[emp] ([id], [name]) VALUES (3, N'venu')
INSERT [dbo].[emp] ([id], [name]) VALUES (4, N'+balu')
INSERT [dbo].[emp] ([id], [name]) VALUES (5, N'evaé')
INSERT [dbo].[emp] ([id], [name]) VALUES (6, N'vdàed')
INSERT [dbo].[emp] ([id], [name]) VALUES (7, N'rh')
Based on this data, I need an output as shown here:

  Id |name
  ---+------
    1|veani
    2|ravi
    3|venu
    4|balu
    5|evae
    6|vdaed
    7|rh
I have tried like below replace (replace(name,'?',''),'+','') name from emp

This query is not working when other special characters are available.

Please let me know how to write query to achieve this task in SQL Server.

Answer 1

Hi @harinathu

You could use TRANSLATE function (SQL Server 2017 14.x and later) first to convert all the special characters into same character say '-'. And then use REPLACE function.

Please check this query:

SELECT TRANSLATE(name,'~@#$%&*()!?+',REPLICATE('-',12)) AS VAL1 
      ,REPLACE (TRANSLATE(name,'~@#$%&*()!?+',REPLICATE('-',12)),'-','') AS VAL2
FROM emp

Note: The argument 12 equals the length of the special characters you are looking for.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Maybe this article will help:
https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server