Removing part of string before and after specific character using Transact-SQL string functions

Problem

Today, one of the developers come to me and asked me the question that is there any T-SQL function that he could use to remove everything before and after a specific character in string. For example, if the table contains the full names of the people in the format as firstname comma surname (Farooq,Basit). He would like to retrieve the first and surnames of people in separate columns of result set.

Solution

The easiest solution that comes to my mind for this problem is to use LEFT and REPLACE string function combined CHARINDEX and LEN string function.

To remove the part of string after the specific character, you use these transact-sql string functions as follow:

SELECT LEFT(string_expression, CHARINDEX(expression_to_find, string_expression) - 1)

To remove the part of string before the specific character, you use these transact-sql string functions as follow:

SELECT REPLACE(SUBSTRING(string_expression, CHARINDEX(expression_to_find, string_expression), LEN(string_expression)), string_pattern, string_replacement)

Demo

For example, I created the following table that contains the sample dummy data. See screen shot below that shows the format of data in this sample table:

tsql_split01

Below is the query that splits the data base on comma(,) in FullName column to FirstName and Surname :

SELECT   [FullName]
	    ,LEFT([FullName], CHARINDEX(',', [FullName]) - 1) AS [Surname]
	    ,REPLACE(SUBSTRING([FullName], CHARINDEX(',', [FullName]), LEN([FullName])), ',', '') AS [FirstName]
FROM    Employee

Example Output

tsql_split02

Hope you will like this post…:)

Advertisements

8 thoughts on “Removing part of string before and after specific character using Transact-SQL string functions

  1. I have something more complicated. I a field that has numbers and letters or characters. The content always begins with number and continues until a space or letter or character. I need to get the number and split everything to the right of the number beginning with the space or character to a new field. example,

    40 A2, split to 40 in one field and A2 in another
    33 1/2, split to 33 in one field and 1/2 in another
    354D, split to 354 in one field and D in another
    43-R, split to 43 in one field and -R in another
    263 Main, split to 263 in one field and Main in another

    Anyone have any suggestions?

    Like

  2. I have a similar need but I have to get first, middle, last names from a string formatted as LastName/FirstName/MiddleName. Any ideas how I might do this? Thanks

    Like

  3. Hi I have a query to split the column MYCOLUMN into 2 columns based on character length of 32759 .
    Characters which are below 32759 into 1 cell and above characters 32759 into other cell like MYCOLUMN 1 and MYCOLUMN 2.Please advise .

    Like

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