I have a sample text that contains '-' or ' '. So I want a sql replace statement that replaces both '-' and ' ' with ''.
My query is:
SELECT REPLACE('SQL-Tu torial','-',' ','');
Desired outcome:
SQLTutorial
Error: I get error for Replace function arguments.
The replace function requires 3 argument(s).
Any help?
You can't use 3 parameters in a REPLACE function. Instead you can use it twice just like below :
SELECT REPLACE(REPLACE('SQL-Tu torial', '-', ''), ' ', '');
Output :
SQLTutorial
Nest two REPLACE functions.
REPLACE(REPLACE())
You can also define a table with all the characters you want to replace and then use a single replace statement while selecting from the table:
declare @charToReplace table (char_to_replace varchar(1))
declare @tempString varchar(max) = 'SQL-Tu torial'
insert into @charToReplace values ('-'), (' ')
select @tempString = replace(@tempString, char_to_replace, '')
from @charToReplace
select @tempString as result
Result:
Replace() would do that.If you have more than two characters to be replaced, you can use TRANSLATE that came in 2017. Below example replaces "-", " " and "@" with "#" using TRANSLATE and then replaces "#" with "" using REPLACE. I.e., replace "-", " ", "@" and "#" with "". TRANSLATE doesn't buy you anything if you only want to substitute two characters, though.
