T-SQL select special characters from nvarchar field

Question

I'm looking for a select statement in which I can refine values in a nvarchar field. For example: the input value is '!ab>c2 ghf5 ksj@_+wwl9' and output must be 'abc ghf ksjwwl'. How can I remove some extra characters within a text with SQL regex or other ways?

Answer 1

I don't know if this helps, but you can define a table with all characters / phrases you want to replace and do it like this:

DECLARE @CharsToReplace TABLE(SearchFor VARCHAR(100),ReplaceWith VARCHAR(100));
INSERT INTO @CharsToReplace VALUES('1',''),('2',''),('3',''),('4',''),('5',''),('6',''),('7',''),('8',''),('9',''),('0','');

DECLARE @YourString VARCHAR(100)='abc2 ghf5 ksjwwl9';

SELECT @YourString=REPLACE(@YourString,SearchFor,ReplaceWith)
FROM @CharsToReplace;

SELECT @YourString;

The result

abc ghf ksjwwl

If you'd define values in ReplaceWith this would work too.

UPDATE A function you can use in any select

Attention: This will be slow!

CREATE FUNCTION dbo.DeleteChars(@CharList NVARCHAR(MAX),@string NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) 
AS
BEGIN
    DECLARE @List XML=(SELECT CAST('<x>'+ REPLACE(@CharList,',','</x><x>')+'</x>' AS XML));
    SELECT @string=REPLACE(@string,ch.value(N'.',N'nvarchar(max)'),'')
    FROM @List.nodes('/x') AS A(ch);
    RETURN @string;
END
GO

--Test this with a mock-up table

DECLARE @SomeTable TABLE(SomeColumn VARCHAR(100));
INSERT INTO @SomeTable VALUES('abc2 ghf5 ksjwwl9'),('123 abc 456 hello');

--This is the char list (comma separated)

DECLARE @CharList VARCHAR(100)='1,2,3,4,5,6,7,8,9,0';

--This is how you call it

SELECT dbo.DeleteChars(@CharList,SomeColumn)
FROM @SomeTable
GO

--Clean up

DROP FUNCTION dbo.DeleteChars;

The result

abc ghf ksjwwl
abc  hello