Removing white spaces and special characters from SQL-CodePudding

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

CodePudding user response：

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