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…:)

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

  3. 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

    Reply

  4. 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

    Reply

  7. 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

    Reply

  9. Great… I have almost similar condition as below.
    I need to break from customer to customer1, customer2, customer3 and so on depends on the number of series. I tried but still not luck.

    Customer Customer1 Customer2 Customer3 Customer4
    6_17368 6 17368
    6_1_8_0 6 1 8 0
    6_22350 6 22350

    Like

    Reply

  10. Great post ,for right another alternative is replace(replace(Fullname,left (Fullname,CHARINDEX(‘\’,Fullname)-1),’ ‘),’\’,’ ‘)

    Like

    Reply

Leave a comment