Tried some ways to eliminate regex in MSSQL. Example:
If(CHARINDEX(‘$’, @name, len(@name)-1) >0)
set @name = SUBSTRING(@name, 0 , len(@name)-1)
What if I need to filter most of the regex? @#$%^&**()_+=, there’ll be alot If Else statement
Finally found a solution, create a function as below:
CREATE FUNCTION [dbo].fn_cleanchars
(@str VARCHAR(8000), @validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX(‘%[^' + @validchars + ']%’,@str) > 0
SET @str=REPLACE(@str, SUBSTRING(@str ,PATINDEX(‘%[^'
+ @validchars +']%’,@str), 1) ,”)
RETURN @str
END
And from your SQL:
SELECT dbo.fn_cleanchars(‘testIng 328-8743$# yedmd’,’0-9A-Za-z ‘) as name
Those bold are the characters that are valid and should remain.
The output will be as below:
