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.

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:

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

Hope you will like this post…:)