CODEWITHSUNDEEP
sqlsql-serverregext-sql
sweety
sweety

Reputation: 75

How to remove all special characters from a column name string using regular expressions

I want to remove all the special characters like $, %, #, etc from the column name of my table in SQL Server 2018. how to do it with the help of regular expressions?

If the value in my column is "S$d#@gh", the output should be -- "Sdgh"

Thank you.

Upvotes: 4

Views: 52133

Answers (5)

андрей дубовой
андрей дубовой

Reputation: 1

I have used another function. Negation of normal number and uppercase and lower-case letters.

CREATE FUNCTION dbo.RemoveSpecialCharacters( @str VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @expres  VARCHAR(max) = '%[^a-zA-Z0-9 ]%'

WHILE PATINDEX( @expres, @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')
RETURN @str
END

Upvotes: 0

morganherg
morganherg

Reputation: 73

Check this link, this one worked for me for a similar problem where I needed to remove alphabetical characters and special characters so I was left with only numbers. https://www.c-sharpcorner.com/blogs/how-to-remove-characters-special-symbol-from-string-using-sql-function

Create function [dbo].[RemoveCharSpecialSymbolValue](@str varchar(500))  
returns varchar(500)  
begin  
declare @startingIndex int  
set @startingIndex=0  
while 1=1  
begin  
set @startingIndex= patindex('%[^0-9.]%',@str)  
if @startingIndex <> 0  
begin  
set @str = replace(@str,substring(@str,@startingIndex,1),'')  
end  
else break;  
end  
return @str  
end

Then use @Thiyagu Select Function(Column) solution

Upvotes: 1

Bohemian
Bohemian

Reputation: 425063

First replace all unwanted characters with a single unwanted character, then replace that with a blank:

select replace(translate('foo$#@bar', '$#@', '$$$'), '$', '')

Output:

foobar

See live demo.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

SQL Server now supports translate(). This allows you to do:

select replace(translate(column, ',~@#$%&*().!', replace(' ', 12)), ' ', '')

The only trick is that the replacement string of spaces needs to be exactly the same length as the characters you are looking for.

Upvotes: 1

Thiyagu
Thiyagu

Reputation: 1330

Try This:

DECLARE @str VARCHAR(400)='S$d#@gh'
DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'

WHILE PATINDEX( @expres, @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')
SELECT @str

You can Create a SCALAR FUNCTION & pass this Column

CREATE FUNCTION dbo.Remove_SpecialCharacters( @str VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'

WHILE PATINDEX( @expres, @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')
RETURN @str
END

O/P

SELECT dbo.Remove_SpecialCharacters(COLUMNNAME),* FROM TABLENAME

Upvotes: 6

Related Questions