CODEWITHSUNDEEP
sqlsql-serverreplace
Sharky99x
Sharky99x

Reputation: 3

SQL - Replace multiple different characters with a single character within a string?

I want to clean up a string and replace various characters with an underscore_ within MS SQL.

The following code can achieve this :

DECLARE @string nvarchar(MAX)
DECLARE @new_string nvarchar(MAX)
SET @string = 'This is.my string/That Needs=cleaning'

SET @new_string = REPLACE(REPLACE(REPLACE(REPLACE(@string, ' ', '_'), '.', '_'), '/', '_'), '=', '_')

SELECT @new_string

This will then return 'This_is_my_string_That_Needs_cleaning'

I will use this to clean up filenames.

Is there a more efficient method, maybe via regex ?

Upvotes: 0

Views: 236

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

SQL Server 2017+ now supports translate():

select @new_string = translate(@string, ' ./=', '____')

Here is a db<>fiddle.

Upvotes: 1

Related Questions