TSQL - Remove special characters from a result

Question

I have a select compares data between tables and as of result, returns document numbers as for example like that:

date           docnumber          magnumber          desc
Monday     DOC/X500/31321321    MAG/21321/X500   [RXC/X500/02132131][]

I would want to remove the special characters [][] from a result of a column 'desc'. Tried result/trim but It didn't give me a result I wanted.

Answer 1

Use REPLACE function:

REPLACE(REPLACE(desc , ']' , ''),'[','')

https://msdn.microsoft.com/en-us/en-en/library/ms186862.aspx?

Answer 2

If you also want to remove white space use LTRIM and RTRIM

LTRIM(RTRIM(REPLACE(REPLACE(desc,'[',''),']','')))

OR

REPLACE(REPLACE(desc,'[',''),']','')

Answer 3

another solution is creating a useful function like below:

Create Function dbo.[RemoveUnWantedCharacters](@Input NVARCHAR(1000))
Returns NVARCHAR(1000)
AS
Begin
    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z0-9/]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
    Return @Temp
End

Like here: https://stackoverflow.com/a/1008566/1068246

declare @test nvarchar(100) = '[RXC/X500/02132131][]';
select dbo.[RemoveNonAlphaCharacters](@test);

returns --> RXC/X500/02132131

Regards.