How to remove special characted and numerics in sql server

Question

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

Sachin Singh · Answer

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)),'#','')

Jignesh Kumar · Answer

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 Gami · Answer

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

Pasang Tamang · Answer

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) ='paul.raj12@T-mobile.com' Declare @UserName varchar(50) Set @UserName = (Select top 1 * from STRING_SPLIT(@Value, '@')) Select dbo.DeleteNonAlphaCharacters(@UserName) Regards, Pasang