Home > SQL Server > Q&A – Removing special characters at the end of the string in SQL Server

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

Sophia123
Emma#1
Olivia$23
Aiden???
Jackson!%
Ethan_123%

These are the names that I choose for this experiment. Following is the step by step approach to solve this problem

Solution

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.

SQL Query

Select 
REVERSE(
    SUBSTRING(
        REVERSE(Firstname), 
        PATINDEX(
            '%[a-z][A-Z]%', 
            REVERSE(Firstname)
            ), 
        LEN(Firstname)
    )
) 
as 'Firstname'
from TestNames
GO

Output
Sophia
Emma
Olivia
Aiden
Jackson
Ethan

This is the method I used to solve this problem. If you have any other solution, Please drop it in the comment section.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: