How to remove special characted and numerics in sql server

Paul Rajs

Hi Developers,

i need to remove special characters and numerics in my sql query.

my code is

DECLARE @USErId AS VARCHAR(100)='[email protected]'

SELECT DISTINCT

CASE
   WHEN ISNULL(@USERID,'') LIKE '%t-mobile.com%'
   THEN REPLACE(REPLACE(REPLACE(@USERID,SUBSTRING (@USERID,CHARINDEX( '@', @USERID ) + 1, LEN(@USERID)),''),'@',''),'.','')
ELSE
   SUBSTRING(@USErId, 1, CHARINDEX('.', @USERID)-1) +REPLACE(SUBSTRING(SUBSTRING(@USERID, CHARINDEX('.', @USERID) + 1, 1000), 1,
CHARINDEX('@', SUBSTRING(@USERID, CHARINDEX('.', @USERID) + 1, 1000))-1),
REVERSE(LEFT(reverse(SUBSTRING(SUBSTRING(@USErId, CHARINDEX('.', @USERID) + 1, 1000), 1,
CHARINDEX('@', SUBSTRING(@USERID, CHARINDEX('.', @USERID) + 1, 1000))-1)),
PATINDEX('%[0-9][^0-9]%', reverse(SUBSTRING(SUBSTRING(@USERID, CHARINDEX('.', @USERID) + 1, 1000), 1,
CHARINDEX('@', SUBSTRING(@USERID, CHARINDEX('.', @USERID) + 1, 1000))-1)) ))),'')
   END AS 'Original Owners',
CASE
   WHEN ISNULL(@USERID,'') LIKE '%t-mobile.com%'
   THEN ISNULL(@USERID,'')
ELSE ''
   END AS 'OriginalOwnerEmail'

My expected output = paulraj

if anyone know how to do this kindly suggest me to done this task

Regards & Thanks

Paul.S

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar3y ago

Hello,

Please use this one

DECLARE @str VARCHAR(25), @characterToRemove varchar(100)
SET @str = '@#@@#%@Jignes2655&$h##'
SET @characterToRemove= '%[~,@,#,$,%,&,*,(,),1,2,3,4,5,6,7,8,9,0]%'
WHILE PATINDEX(@characterToRemove, @str ) > 0
          SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @characterToRemove, @str ), 1 ),''),'-',' ')
SELECT @str

Rajesh Gami3y ago
https://www.sqlneed.com/2021/01/how-to-find-and-remove-special/

https://www.c-sharpcorner.com/blogs/how-to-remove-characters-special-symbol-from-string-using-sql-function

https://www.sqlshack.com/replace-ascii-special-characters-sql-server/

https://stackoverflow.com/questions/31050086/find-and-replace-all-special-character-in-sql
+3

Pasang Tamang3y ago

Hi,

In one of my project I used function for similar case. Please check below SQL scripts.

Function Code:

Create Function [dbo].[DeleteNonAlphaCharacters](@value VarChar(500))
Returns VarChar(500)
AS
Begin
    Declare @Regex as varchar(50)
    Set @Regex = '%[^a-z]%'
    While PatIndex(@Regex, @value) > 0
        Set @value = Stuff(@value, PatIndex(@Regex, @value), 1, '')
    Return @value
End

Before you call this function, you have to split your text by @. Below scripts shows how I did for an example

Declare @Value as varchar(50) ='[email protected]'
Declare @UserName varchar(50)
Set @UserName = (Select top 1 * from STRING_SPLIT(@Value, '@'))
Select dbo.DeleteNonAlphaCharacters(@UserName)

Regards,
Pasang

Sachin Singh3y ago

you can use replace and translate functions.

--testcase: 'sachin1234567890!@$%^&*()_-' should be 'sachin'
declare @str varchar(150)='sachin1234567890!@#$%^&*()_-';

select Replace(Translate(Translate(@str,'1234567890', Replicate('#',10)),'!@#$%^&*()_-',Replicate('#',12)),'#','')