CODEWITHSUNDEEP
sqlsql-servert-sql
Rick
Rick

Reputation: 1559

Replace function in SQL

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?

Upvotes: 4

Views: 15680

Answers (4)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

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

Upvotes: 9

Andrea
Andrea

Reputation: 12355

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:

enter image description here

Upvotes: 5

Tibor Karaszi
Tibor Karaszi

Reputation: 389

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.

Upvotes: 1

Tab Alleman
Tab Alleman

Reputation: 31775

Nest two REPLACE functions.

REPLACE(REPLACE())

Upvotes: 6

Related Questions