T-SQL query to replace special characters with a specific character

Question

I am using SQL Server 2014 and I have the following T-SQL query:

select p9.[Name]
from [CleanGuestNames] p outer apply
     (select replace(p.[Name], '@', '*') as [Name]) p1 outer apply
     (select replace(p1.[Name], '&', '*') as [Name]) p2 outer apply
     (select replace(p1.[Name], '\', '*') as [Name]) p3 outer apply
     (select replace(p1.[Name], '?', '*') as [Name]) p4 outer apply
     (select replace(p1.[Name], '/', '*') as [Name]) p5 outer apply
     (select replace(p1.[Name], ':', '*') as [Name]) p6 outer apply
     (select replace(p1.[Name], ';', '*') as [Name]) p7 outer apply
     (select replace(p1.[Name], '.', '*') as [Name]) p8 outer apply
     (select replace(p2.[Name], ',', '*') as [Name]) p9 ;

Codes above are supposed to replace all characters mentioned therein with a * in the [Name] column.

When I run the query, I still get records as follows from the [Name] column:

Alex / Sandrine SINNOF / VAN ACK
Peter / Jane KELLY

Expected Results:

Alex * Sandrine SINNOF * VAN ACK
Peter * Jane KELLY

What am I doing wrong here?

Because you keep replacing on p1.[Name], not p1.[Name], then p2.[Name], then p3.[Name]. Though, personally, I would suggest just nesting the REPLACE functions. If you were on a fully supported version of SQL Server, then TRANSLATE would be a much better option. — Thom A
– Thom A ♦, Commented May 25, 2021 at 12:40

Thom A · Accepted Answer · 2021-05-25 12:48:44Z

As mentioned in the comment, this is really a typographical error, as you are consistently referencing p1.[name] and so you are, in the end, only removing the characters @, &, and ,. You need to be refereing p2.[name], p3.[name], etc:

select p9.[Name]
from dbo.[CleanGuestNames] p outer apply
     (select replace(p.[Name], '@', '*') as [Name]) p1 outer apply
     (select replace(p1.[Name], '&', '*') as [Name]) p2 outer apply
     (select replace(p2.[Name], '\', '*') as [Name]) p3 outer apply
     (select replace(p3.[Name], '?', '*') as [Name]) p4 outer apply
     (select replace(p4.[Name], '/', '*') as [Name]) p5 outer apply
     (select replace(p5.[Name], ':', '*') as [Name]) p6 outer apply
     (select replace(p6.[Name], ';', '*') as [Name]) p7 outer apply
     (select replace(p7.[Name], '.', '*') as [Name]) p8 outer apply
     (select replace(p8.[Name], ',', '*') as [Name]) p9;

Personally, however, I would suggest nesting all the REPLACE functions:

SELECT R.[Name]
FROM dbo.CleanGuestNames CGN
     CROSS APPLY (VALUES(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(p.[name],'@',''),'&',''),'\',''),'?',''),'/',''),':',''),';',''),'.',''),',','')))R([Name]);

If you were on a supported version of SQL Server, you could make it much more succinct with TRANSLATE:

SELECT T.[Name]
FROM dbo.CleanGuestNames CGN
     CROSS APPLY (VALUES(REPLACE(TRANSLATE(p.[name],'@&\?.:;.',',,,,,,,,'),',','')))T([Name]);

Collectives™ on Stack Overflow

T-SQL query to replace special characters with a specific character

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related