Q&A – Removing special characters at the end of the string in SQL Server
Recently, I have received a question from a reader asking me how to remove special characters at the end of the string without using TSQL. This look very simple until I started thinking about the answer. Because, the requirement is not to create TSQL to complete this task. I did try different approach and finally got the a String Function in SQL Server that helped me solving this problem.
Sample source data is given below
These are the names that I choose for this experiment. Following is the step by step approach to solve this problem
Step 1: Create a Test table
Create table TestNames ( Firstname varchar(40) ) GO
Step 2: Insert sample values into the test table
Insert into TestNames (Firstname) values('Sophia123') Insert into TestNames (Firstname) values('Emma#1') Insert into TestNames (Firstname) values('Olivia$23') Insert into TestNames (Firstname) values('Aiden???') Insert into TestNames (Firstname) values('Jackson!%') Insert into TestNames (Firstname) values('Ethan_123%') GO
Step 3: This is the final step to remove the special character and numbers at the end of the string
I used PATINDEX() string function to accomplish this task. I have also make use of REVERSE(), LEN() and SUBSTRING() functions to simplify the data cleaning process.
Step a: We need to Reverse the whole string
Step b: Find the first occurrence of the alphabets both lower and upper case using wild card characters.
Step c: Extract the SubString() starting from the position we got it in the step b to total length of the string.
Step d: Now, Reverse the sub string to get the desire output.
Select REVERSE( SUBSTRING( REVERSE(Firstname), PATINDEX( '%[a-z][A-Z]%', REVERSE(Firstname) ), LEN(Firstname) ) ) as 'Firstname' from TestNames GO
This is the method I used to solve this problem. If you have any other solution, Please drop it in the comment section.