Removing white spaces and special characters from SQL

Question

I have a table where I have a ColumnA which has data with white spaces and special characters. I want to generate ColumnB with the data from ColumnA with the removal of white spaces and special characters. For example, ColumnA has values like: N/A @email Hot-topic #sql#% White paper. I want a new column with values: NA email HotTopic sql Whitepaper I tried below SQL in SSMS, but it is not working completely. Could someone help me out? SELECT code, REPLACE(REPLACE(code, TRIM(TRANSLATE(code,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz',' ')) ,'') ,' ','') FROM SAMP It is not working for the record with value: #sql#% JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

Answer 1

Added as a wiki answer in order to retain the comment made by @lptr. Query by @lptr explanation mine (@DaleK). Your attempt was close, but only worked for single characters... the one that failed was because you had multiple characters that needed replacing and once you remove the white space they are all next to each other and don't match the original string anymore. This answer cleverly replaces all the letter characters with a "*" using translate as step 1, then using translate again on the original column value, replaces all the non-letter characters with a "*" as step 2, then finally replaces all "*" characters with an empty string. Note also the use of replication to avoid typing the same character in multiple times. create table samp(code varchar(50)); insert into samp(code) values ('N/A'), ('@email'), ('Hot-topic'), ('#sql#%'), ('White paper. '); select s.code, n.nonletters, l.letters from samp as s cross apply (values(translate(s.code, 'abcdefghijklmnopqrstuvwxyz', replicate('*', 26)))) as n (nonletters) cross apply (values(replace(translate(s.code, n.nonletters, replicate('*', len(n.nonletters+'.')-1)), '*', ''))) as l (letters);